MySQL : Load Data Infile with AI PRIMARY Key and On Duplicate Key Update

Question

I find a correct solution how Load Data Infile in MySQL can work with On Duplicate Key Update. Here is the code I use (get it from stack overflow):

CREATE TEMPORARY TABLE temporary_table LIKE employee_table;

SHOW INDEX FROM temporary_table;
DROP INDEX `PRIMARY` ON temporary_table;

LOAD DATA INFILE 'csv/employee_table'
INTO TABLE temporary_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' 
(
    `AutoID`, `Name`, `Age`, `Salary`
)

SHOW COLUMNS FROM employee_table;
INSERT INTO employee_table
SELECT * FROM temporary_table
ON DUPLICATE KEY UPDATE AutoID = Values(AutoID);

DROP TEMPORARY TABLE temporary_table;

This code above is working properly to update the table with new records that have different ID only. However, when the PRIMARY key is auto increment (AI), it fails to work. It shows that this below code cannot be run on auto increment PRIMARY key.

DROP INDEX `PRIMARY` ON temporary_table

Here is the error message:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

I am confused since if I don't use the auto increment, it run perfectly. Anyone know how to fix this problem? Thanks in advance.

score 0 · Answer 1 · edited Sep 06 '17 at 10:50

there is two procedure.

1) your problem solution.

mysql> DROP INDEX `PRIMARY` ON test5;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> show create table test5;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test5 | CREATE TEMPORARY TABLE `test5` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table test5 modify `id` int(11) NOT NULL;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> DROP INDEX `PRIMARY` ON test5;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> show create table test5;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test5 | CREATE TEMPORARY TABLE `test5` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `address` varchar(100) DEFAULT NULL,
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2) Best Solution..

we can replace first (two steps) with below single query in the procedure.

1) and 2) we can create new table with same reference structure and without any indexes.

CREATE TEMPORARY TABLE temporary_table SELECT * FROM target_table WHERE 1=0;

Instead of..

1) Create a new temporary table.

CREATE TEMPORARY TABLE temporary_table LIKE target_table;

2) Optionally, drop all indices from the temporary table to speed things up.

SHOW INDEX FROM temporary_table; DROP INDEX PRIMARY ON temporary_table; DROP INDEX some_other_index ON temporary_table;

you can also refer below link.

MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE

MySQL : Load Data Infile with AI PRIMARY Key and On Duplicate Key Update

1 Answers1