python - How can I configure MYSQL DB to accept NULL for primary key in the INSERT statement and then generate unique primary key

Question

I am using the code below from this stackoverflow answer to copy a row in a table and create a new row in the same table, but with the same data and a unique primary key. I am running the code using the mysql-connector-python module.

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable_1 SET primarykey = NULL;
INSERT INTO table SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;

This code runs on the production database, but gives the following error on my local test db.

IntegrityError: 1048 (23000): Column 'primaryKey' cannot be null

Local DB:

• Version: MYSQL==5.6.47
• Compiled For: osx10.15 (x86_64)
• DB Client: mysql-connector-python==2.1.3

Production DB:

• Version: MYSQL==5.6.40
• Compiled For: Linux (x86_64)
• DB Client: mysql-connector-python==2.1.3

I use MYSQL Workbench to export the schema and data from the production data base and import it into my local test database.

Is there some configuration setting for a MYSQL DB that is the problem? I am not sure what the cause is for the difference in database behaviour.

标签： pythonmysql

Answer 1

只需从INSERT侧面放下该列，您就可以插入自己：

INSERT INTO table (a, b, c) SELECT a, b, c FROM table;

您必须以完全相同的顺序指定列，省略id一个。