MySQL无法插入记录的无符号主键为零 [英] mysql can't insert record with unsigned primary key being zero

问题描述

我试图将一条记录插入dim_channel表中，并将主键(无符号int)设置为零.

I am trying to insert one record into dim_channel table with zero for the primary key (unsigned int).

Mysql命令:

INSERT INTO dim_channel 
set channel_id=0,name='Other',parent_channel_id=0,parent_channel_name='Other';

结果:

select * from dim_channel;
+------------+-------+-------------------+---------------------+
| channel_id | name  | parent_channel_id | parent_channel_name |
+------------+-------+-------------------+---------------------+
|          1 | Other |                 0 | Other               |
+------------+-------+-------------------+---------------------+

请注意，channel_id的值为1，而不是我期望的0.

Please note that channel_id got value 1, not 0 as I expected.

任何人都知道为什么会这样.

Any one knows why this happens.

顺便说一句，我可以将记录更新为: 更新dim_channel设置channel_id = 0，其中channel_id = 1;

By the way, I can update the record as: update dim_channel set channel_id=0 where channel_id=1;

只想知道为什么我不能首先插入channel_id = 0的记录.

Just want to know why I can't insert the record with channel_id=0 at the first place.

非常感谢.

====== MySQL命令供您测试====

====== MySQL command for you to test ====

-创建表格

CREATE TABLE `dim_channel` (
  `channel_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(80) DEFAULT NULL,
  `parent_channel_id` int(10) unsigned NOT NULL DEFAULT '0',
  `parent_channel_name` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`channel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

-插入记录

INSERT INTO dim_channel set channel_id=0,name='Other',parent_channel_id=0,parent_channel_name='Other';

-查看结果

select * from dim_channel;

推荐答案

这是因为您在该字段上具有自动递增的主键.如果您在插入时为该值分配NULL或0，它将在表顺序中为您明确提供下一个数字.

It is because you have an auto-increment primary key on that field. If you assign NULL or 0 for that value on insert it will explicitly give you the next number in the sequence for the table.

这篇关于MySQL无法插入记录的无符号主键为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！