Mysql – Map auto increment ids on multi-row insert

auto-incrementinsertMySQL

I have the following table with an AUTO_INCREMENT column.

CREATE TABLE animals ( 
  id MEDIUMINT NOT NULL AUTO_INCREMENT, 
  name CHAR(30) NOT NULL, 
  PRIMARY KEY (id) 
);

Usually I load multiple rows with one INSERT like this:

INSERT INTO animals (name) 
VALUES  ('dog'),('cat'),('cow');

Sometimes though I want assign to each value its ID. Like this:

1 -> dog 
2 -> cat 
3 -> cow

If I insert simple values I can select them back. But when I insert JSON documents I'm worried about using syntax like SELECT WHERE json IN (...).

I thought to get MAX(id) before selection, then get last_insert_id after selection and using count of inserted rows assign id to each. But I haven't found in documentation anything about order that rows in insert are processed, so I'm worried I won't be able to know what the ID's are.

Inserting rows one by one and get last_insert_id for each is not good solution. Is there a way to insert multiple rows at once and return to the application the IDs for each row?

Best Answer

Don't use MAX(id), that could be wrong if to another connection doing inserts. last_insert_id, on the other hand, is local to the session, so it is unaffected by other connections.

INSERT INTO animals (name) 
    VALUES  ('dog'),('cat'),('cow');
SELECT last_insert_id();  -- Let's say you get 34
-- Then dog=34, cat=35, cow=36

But -- beware of innodb_autoinc_lock_mode -- the setting of this can affect what I discussed above. This is especially true if, for whatever reason, some of the rows fail to insert. (Example: you have UNIQUE(name) and already have cat in the table.) In at least one of the modes, 35 is 'burned'.

The following goes beyond what you have asked for...

I decided is the best way to deal with a similar problem involves two steps. First, let me explain what is going on. I am 'normalizing' the names of animals. Instead of using "dog", I want to use the id associated with dog. I have lots of data coming in, and I want to batch insert a bunch of animal names into the table, plus get the ids for them. I am very likely to have lots of dups, but I don't want to 'burn' an id for every dup.

So...

INSERT ... SELECT ... LEFT JOIN ...  -- insert only the 'new' animals.   
UPDATE ... JOIN ...  -- pull the ids into another table.

The details are spelled out here .

Related Solutions

Mysql – How to make MySQL auto increment in random order

Here's what I would do. I hope I did understand your question correctly. It's a workaround and perhaps not very elegant:

create a random field, and then an AUTO_INCREMENT field ("ID") as the single field of a primary key, order by RND etc
create a temporary table with MIN(ID) for each combination of Col1/2 (first, but randomly):


SELECT
  MIN(ID) AS MIN_ID, Col1, Col2 
FROM 
  table 
GROUP BY 
 Col1, Col2

add a second INT field to be the first_index
update the table and set first_index to be the difference between the minimum value and the running value of the autoincremented field:


UPDATE 
  table t, temp_table tmp
SET 
  t.first_index = (t.ID - tmp.MIN_ID) + 1
WHERE 
  t.Col1=tmp.Col1 AND t.Col2=tmp.Col2

optionally remove the random and ID field, and add a new PK with the three fields Col1/Col2/first_index (auto incremented) for following inserts

Mysql – Drop auto increment hack w/o alter table

I tried something similar just now

Here is MySQL for My PC

mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.

Here are Steps 1-4

mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6

C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7

mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???

Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?

You got guts, @atxdba !!!

Best Answer

Related Solutions

Mysql – How to make MySQL auto increment in random order

Mysql – Drop auto increment hack w/o alter table

Related Question