Multiple Auto Increment in MySQL

How to use more than 1 auto-increment column in MySQL

Since the auto-increment cannot be applied to multiple to rows and there no option for sequence in MySQL. You can use triggers for the unique update of the row with datetime.

Change to table creation to be of single auto-increment row.

CREATE TABLE `users` ( `User` VARCHAR(20) NOT NULL,
 `cookieID` INT(20) NULL DEFAULT NULL,
 `sessionID` INT(20) NULL DEFAULT NULL AUTO_INCREMENT ,
 `Geo` VARCHAR(30) NULL DEFAULT NULL,
 PRIMARY KEY (`User`), UNIQUE (`cookieID`), UNIQUE (`sessionID`), UNIQUE (`Geo`));

Create a trigger on the same table as below. You can set the unique values under the SET for as many column as you want.

CREATE DEFINER=`root`@`localhost` TRIGGER `users_BEFORE_INSERT` BEFORE INSERT ON `users` FOR EACH ROW BEGIN
SET 
NEW.cookieID = (SELECT curdate()+curtime());
END

Now when you insert into the table as below.

 insert into `users`(`User`) values("test");

You table looks like this.

User  cookieID  sessionID  Geo
test  20315169  0          NULL

How to create two auto increment columns using MySQL?

There are a variety of problems with your approach.

First, DELIMITER is a built-in command of the mysql client, it is not recognized by the MySQL Server's SQL parser. Also, I wonder why you are not using https://docs.liquibase.com/change-types/pro/create-function.html

Second, your method of simulating a second auto-increment is flawed. It suffers from a race condition, because if more than one concurrent transaction is inserting a row, they will both get the same result from your SELECT COUNT(*) query, and therefore both attempt to insert the same value for the user_id.

The real auto-increment mechanism in MySQL works outside of transaction scope, so multiple sessions are assured to read the same increment value per table. There is also a brief table-lock while each session increments the table's counter.

To make your function safe from race conditions, you would have to lock the table, to ensure that only one session at a time can read the value.

Third, the auto-increment should be based on MAX(USER_ID), not COUNT(USER_ID) if you're going to calculate the next higher value. If you were to delete a few rows from the table, then COUNT(USER_ID) would generate a value lower than the max value.

Lastly, I can't understand why you would want to make user_id auto-increment at all for an orders table. You would really want every order to generate a new user_id? Aren't user_id's supposed to reference a currently existing user?

multiple auto increment in mysql

You could use a trigger, cf http://dev.mysql.com/doc/refman/5.1/de/create-trigger.html

Mysql table with more than one Auto-Incremented column

It is not possible.There can be only one auto-increment column and it must be defined as a key in MySQL.
But You can do it by using trigger for detail go this link CREATE TRIGGER

mysql two column primary key with auto-increment

if you are using myisam

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

For MyISAM and BDB tables you can
specify AUTO_INCREMENT on a secondary
column in a multiple-column index. In
this case, the generated value for the
AUTO_INCREMENT column is calculated as
MAX(auto_increment_column) + 1 WHERE
prefix=given-prefix. This is useful
when you want to put data into ordered
groups.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

For your example:

mysql> CREATE TABLE mytable (
    ->     table_id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->     database_id MEDIUMINT NOT NULL,
    ->     other_column CHAR(30) NOT NULL,
    ->     PRIMARY KEY (database_id,table_id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mytable (database_id, other_column) VALUES
    ->     (1,'Foo'),(1,'Bar'),(2,'Baz'),(1,'Bam'),(2,'Zam'),(3,'Zoo');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable ORDER BY database_id,table_id;
+----------+-------------+--------------+
| table_id | database_id | other_column |
+----------+-------------+--------------+
|        1 |           1 | Foo          |
|        2 |           1 | Bar          |
|        3 |           1 | Bam          |
|        1 |           2 | Baz          |
|        2 |           2 | Zam          |
|        1 |           3 | Zoo          |
+----------+-------------+--------------+
6 rows in set (0.00 sec)

Can MYSQL keep track of multiple Auto Increments based on a column input value?

Both MyISAM and InnoDB support only one auto-increment per table.

Managing any autoincrement-per-group feature cannot work in a storage engine that supports concurrent updates, because of race conditions.

MyISAM supports auto-increment as a secondary column of a key, so it numbers independently for each value in the first column of the same key. But this still won't work for you because MyISAM doesn't support two auto columns per table.

mysql> create table MyTable (
    -> post_id int auto_increment primary key,
    -> tag_name varchar(10),
    -> tag_id int auto_increment,
    -> unique key (tag_name, tag_id)
    -> ) engine=myisam;
ERROR 1075 (42000): Incorrect table definition; there can be only one
auto column and it must be defined as a key

You'll have to number your tag_id manually. And that means you can't support concurrent updates to this table.

IMO, you should re-think what you're trying to do.

---

Related Topics