color auto mysql_zerofill在mysql字段定义中的使用以及auto_increment的注意事项

Zerofill

用于数字类型的定长显示是最适合不过了， 长度不够时，用0填充。

mysql> create table t1(id int(6) zerofill auto_increment primary key, col2 varch

ar(32));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 (col2) values('abc'), ('bcd');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t1;

+--------+------+

| id | col2 |

+--------+------+

| 000001 | abc |

| 000002 | bcd |

+--------+------+

2 rows in set (0.00 sec)

每次都将id值输出成6个字符的宽度。

这种用法，可以大量用于所谓“流水号”的生成上。

比如，想要生成日期_0x的流水号。可以直接拼接，如：

mysql> create table t1(id int(2) zerofill auto_increment primary key, col2 varch

ar(10));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(col2) values('abc'), ('def');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

取流水号：

mysql> select concat(concat(date(now()), '_'), id) from t1;

+--------------------------------------+

| concat(concat(date(now()), '_'), id) |

+--------------------------------------+

| 2012-11-26_01 |

| 2012-11-26_02 |

+--------------------------------------+

2 rows in set (0.00 sec)需要说明的是，这里的id值，可能会溢出。

mysql> insert into t1 values(99, '123abc');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(col2) values('others');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

+-----+--------+

| id | col2 |

+-----+--------+

| 01 | abc |

| 02 | def |

| 99 | 123abc |

| 100 | others |

+-----+--------+

4 rows in set (0.00 sec)

mysql> select concat(concat(date(now()), '_'), id) from t1;

+--------------------------------------+

| concat(concat(date(now()), '_'), id) |

+--------------------------------------+

| 2012-11-26_01 |

| 2012-11-26_02 |

| 2012-11-26_99 |

| 2012-11-26_100 |

+--------------------------------------+

4 rows in set (0.00 sec)

新值'100'超长了。碰到这种情况，可以进行截短处理，请看： mysql> select concat(concat(date(now()), '_'), substr(id, 1, 2)) from t1;

+----------------------------------------------------+

| concat(concat(date(now()), '_'), substr(id, 1, 2)) |

+----------------------------------------------------+

| 2012-11-26_01 |

| 2012-11-26_02 |

| 2012-11-26_99 |

| 2012-11-26_10 |

+----------------------------------------------------+

4 rows in set (0.00 sec)

可是这个截短又不正确，取的是前两位。应该取的是末两位。

改为：

mysql> select concat(concat(date(now()), '_'), substr(id, length(id)-1, 2)) from

t1;

+---------------------------------------------------------------+

| concat(concat(date(now()), '_'), substr(id, length(id)-1, 2)) |

+---------------------------------------------------------------+

| 2012-11-26_01 |

| 2012-11-26_02 |

| 2012-11-26_99 |

| 2012-11-26_00 |

+---------------------------------------------------------------+

4 rows in set (0.00 sec)

Auto_increment

MySQL的中AUTO_INCREMENT类型的属性用于为一个表中记录自动生成ID功能，可在一定程度上代替 Oracle，PostgreSQL等数据库中的sequence。一个表只能有一个AUTO_INCREMENT属性，且该属性必须为主键的一部分。AUTO_INCREMENT属性可以是任何整数类型(tinyint，smallint，int， bigint等)。

在插入记录时让系统为AUTO_INCREMENT属性自动生成值的通常方法在VALUES列表中不指定该属性的值，如若有表t定义如下:

CREATE TABLE t (a INT AUTO_INCREMENT PRIMARY KEY, b INT);

则一般使用如下插入语句:

INSERT INTO t(b) VALUES(1);

即在插入时不指定a的值。

第二种方法是插入时为AUTO_INCREMENT属性指定值为0[/COLOR] ，即:

INSERT INTO t VALUES(0, 1);

与

INSERT INTO t(b) VALUES(1);

的效果是一样的。但这种方法没有第一种方法通用，并且在5.0及其后续版本中只有在SQL MODE中不包含NO_AUTO_VALUE_ON_ZERO时才生效[/COLOR](如果在SQL MODE指定了NO_AUTO_VALUE_ON_ZERO，则真的是插入了0)，不推荐使用。还有其它的方法，如指定AUTO_INCREMENT属性的值为DEFAULT等，也不推荐使用。

获得新生成的AUTO_INCREMENT属性的值也有多种方法。推荐的方法是使用LAST_INSERT_ID()函数，即在INSERT之后马上使用:

SELECT LAST_INSERT_ID();

来获得新生成的AUTO_INCREMENT属性值。

另外还可以用"WHERE auto_col IS NULL"条件选择出新插入的行[/COLOR]，即在INSERT后马上用:

SELECT * FROM t WHERE a IS NULL;

选择出来的将是新插入的行，而非真正的满足"a IS NULL"条件的行。但你要是再执行一次上述查询，则返回的又变成了真正的满足"a IS NULL"条件的行，由于a是主键，因此肯定会返回空集。这看上去很诡异是吗，不过MySQL也不想这么干，但ODBC标准里曾有这种用法，为了支持 ODBC，MySQL也是没办法啊。不过可以将SQL_AUTO_IS_NULL设为0来禁止这一用法。

此外AUTO_INCREMENT属性也给复制带来了麻烦。一般情况下复制AUTO_INCREMENT属性能正确工作，但以下情况还是有问题：

1. INSERT DELAYED ... VALUES(LAST_INSERT_ID())不能被正确复制

2. 存储过程插入的使用AUTO_INCREMENT属性的记录不能被正确复制

3. 通过"ALTER TABLE"命令增加AUTO_INCREMENT属性时在主从节点上产生的值可能是不一样的，因为这个各行AUTO_INCREMENT属性的值取决于物理上的存储顺序。

作者：iihero 发表于2012-11-26 7:44:06 原文链接

阅读：0 评论：0 查看评论