mysql中SERIAL和AUTO_INCREMENT有什么区别

I have come across two ways to increment the ids in mysql automatically.

我有两种方法可以自动增加mysql中的id。

One is SERIAL and 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.

请帮忙。

3 个解决方案

#1

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的引用时要小心，因为该引用列必须是这种类型。

#2

AUTO_INCREMENT is an attribute of 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

SERIAL is an alias that combines column type casting (BIGINT specifically), AUTO_INCREMENT, UNSIGNED and other attributes for 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是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 DEFAULT VALUE是NOT NULL AUTO_INCREMENT UNIQUE的别名。

#3

From mysql doc

来自mysql doc

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

SERIAL是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 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
• 使用主从复制或事务时可能会发生错误