我偶然发现了一个非常奇怪的行为:
假设我们有一个餐桌顾客:
MariaDB [connections]> describe customers;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| customerId | int(11) | NO | PRI | NULL | auto_increment |
| customerName | varchar(50) | NO | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
插入几个值:
insert into customers(customerName) values('Foo');
insert into customers(customerName) values('Bar');
然后删除所有内容并重置自动增量:
DELETE FROM customers;
ALTER TABLE customers AUTO_INCREMENT = 0;
现在,插入一个带有customerId = 0的新值:
INSERT INTO customers(customerId,customerName) VALUES(0,'Site owner');
并查看结果:
MariaDB [connections]> select * from customers;
+------------+--------------+
| customerId | customerName |
+------------+--------------+
| 1 | Site owner |
+------------+--------------+
1 row in set (0.00 sec)
customerId设置为1 !!!!
重复相同的过程,但重置为5并插入5,一切正常:
MariaDB [connections]> delete from customers;
Query OK, 1 row affected (0.00 sec)
MariaDB [connections]> ALTER TABLE customers AUTO_INCREMENT = 5;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [connections]> INSERT INTO customers(customerId,customerName) VALUES(5,'Site owner');
Query OK, 1 row affected (0.00 sec)
MariaDB [connections]> select * from customers;
+------------+--------------+
| customerId | customerName |
+------------+--------------+
| 5 | Site owner |
+------------+--------------+
1 row in set (0.00 sec)
这里发生了什么?如何插入值“ 0”和插入值?(是的,我以后可以编辑,但是由于种种原因,对于我的情况来说这是不切实际的)。
谢谢!
我已经从链接中引出了答案
您可以使用:
SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'
如此处所述,这将防止MySQL将INSERT / UPDATE ID 0解释为下一个序列ID。这种行为将被限制为NULL。
我认为这是应用程序中非常糟糕的行为。您必须非常谨慎地确保它的使用一致,尤其是如果您选择以后实施复制。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句