MySQL mysql中SERIAL和AUTO_INCREMENT有什么区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20021983/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the difference between SERIAL and AUTO_INCREMENT in mysql
提问by Filipe Silva
I have come across two ways to increment the ids in mysql automatically.
我遇到过两种自动增加 mysql 中的 id 的方法。
One is SERIALand other is AUTOINCREMENT.
一种是SERIAL，另一种是AUTOINCREMENT。
So Suppose i want to create a table myfriends. I can create it in two ways like:
所以假设我想创建一个表 myfriends。我可以通过两种方式创建它，例如：
1)
1)
mysql> create table myfriends(id int primary key auto_increment,frnd_name varchar(50) not null);
2)
2)
mysql> create table myfriends(id serial primary key,frnd_name varchar(50) not null);
What is difference between the two ?
两者有什么区别？
OR
或者
Do anyone way has advantages over other ?
有没有人比其他人有优势？
Please Help.
请帮忙。
回答by Filipe Silva
As per the docs
根据文档
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名。
So, be careful when creating a reference to a SERIAL PK, since that reference column has to be of this exact type.
因此，在创建对 SERIAL PK 的引用时要小心，因为该引用列必须是这种确切类型。
回答by zamnuts
AUTO_INCREMENTis an attributeof a specific column of any numeric type (int or float), both signed and unsigned. When rows are inserted it automatically assigns sequential numbers, so you don't have to (e.g. by using LAST_INSERT_ID()). See http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
AUTO_INCREMENT是任何数字类型（int 或 float）的特定列的属性，包括有符号和无符号。插入行时，它会自动分配序列号，因此您不必（例如，使用LAST_INSERT_ID()）。见http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
SERIALis an aliasthat combines column type casting (BIGINTspecifically), AUTO_INCREMENT, UNSIGNEDand other attributesfor a specific column (see quote from docs below). See http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html
SERIAL是一个别名，结合柱型铸造（BIGINT特异性）， AUTO_INCREMENT，UNSIGNED和其它属性的特定列（见从下面的文档引用）。见http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.
SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名。
整数列定义中的SERIAL DEFAULT VALUE 是NOT NULL AUTO_INCREMENT UNIQUE 的别名。
回答by Damodaran
From mysql doc
来自mysql 文档
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.
SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名。
整数列定义中的SERIAL DEFAULT VALUE 是NOT NULL AUTO_INCREMENT UNIQUE 的别名。
If no value is specified for the AUTO_INCREMENT column, MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers. MySQL doesn't automatically decrease the autoincrement value when you delete a row. Reasons are:
如果没有为 AUTO_INCREMENT 列指定值，MySQL 会自动分配序列号。您还可以为列显式分配 NULL 或 0 以生成序列号。当您删除一行时，MySQL 不会自动减少自动增量值。原因是：
- Danger of broken data integrity (imagine multiple users perform deletes or inserts...doubled entries may occur or worse)
- Errors may occur when you use master slave replication or transactions
- 数据完整性损坏的危险（想象多个用户执行删除或插入...可能会出现重复条目或更糟的情况）
- 使用主从复制或事务时可能会出错
