Python MySQLdb 插入主键

Question

是否可以使用 mysqldb 插入到表中并强制转换自动增量值？

例如:

def __init__(self):
  self.connection = MySQLdb.connect(self.v3_host, self.v3_user, self.v3_password, self.v3_database)
  self.cursor = self.connection.cursor()

def insert(self, query):
  try:
    self.cursor.execute(query)
    self.connection.commit()
  except:
    self.connection.rollback()

---

db3 = DatabaseV3()
insert = """INSERT INTO user_avatars (id, user_id, file_id) VALUES (..."""
db3.insert(insert)

ID 是来自数据库的自动递增值，但如果我希望它在不实际更改列的情况下强制为列插入特定值怎么办？

Answer 1

仅仅因为它是自动递增的，并不意味着你不能覆盖这个值:

INSERT INTO user_avatars (id, user_id, file_id) VALUES (23, 42, 56)

你只需要确保这个值没有被使用，否则你会从数据库中得到一个异常:

mysql> create table foo (id INT AUTO_INCREMENT PRIMARY KEY, bar INT);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into foo values (12,1), (23,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from foo;

+----+------+
| id | bar  |
+----+------+
| 12 |    1 |
| 23 |    1 |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into foo (bar) values (13);
Query OK, 1 row affected (0.01 sec)

mysql> select * from foo;
+----+------+
| id | bar  |
+----+------+
| 12 |    1 |
| 23 |    1 |
| 24 |   13 |
+----+------+
3 rows in set (0.00 sec)
mysql> insert into foo values (12, 2);
ERROR 1062 (23000): Duplicate entry '12' for key 'PRIMARY'

如果愿意，您还可以更改主键的值:

mysql> update foo set id = 13 where id = 12 limit 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from foo;
+----+------+
| id | bar  |
+----+------+
| 13 |    1 |
| 23 |    1 |
| 24 |   13 |
+----+------+
3 rows in set (0.00 sec)