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文字の幅に出力します.
この用法は、いわゆる「流水号」の生成に多く用いられる.
例えば、日付_を生成したい0 xの流水番号.次のように直接接続できます.

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)

しかし、このカットは正しくなく、上位2位を取った.取るべきは末尾の2人です.
変更後:

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タイプのプロパティは、Oracle、PostgreSQLなどのデータベースのsequenceの代わりに、1つのテーブルに自動的にIDを生成する機能を記録するために使用されます.1つのテーブルに1つのAUTOしかありませんINCREMENTプロパティで、プライマリ・キーの一部である必要があります.AUTO_INCREMENT属性は、任意の整数タイプ(tinyint,smallint,int,bigintなど)であってもよい.記録挿入時にAUTO_INCREMENT属性に対して自動的に値を生成させる通常の方法は、VALESリストにおいてその属性の値を指定せず、表tの定義があれば以下の通りである:CREATE TABLE t(a INT AUTO_INCREMENT PRIMARY KEY,b INT);一般的には、INSERT INTO(b)VALUES(1);すなわち、挿入時にaの値を指定しない.2つ目の方法は、挿入時にAUTO_INCREMENT属性指定値は0[/COLOR],すなわちINSERT INTO t VALUES(0,1);INSERT INTO t(b)VALES(1);の効果は同じです.しかし、この方法は第1の方法では通用せず、5.0以降のバージョンではSQL MODEにNO_が含まれていないのみである.AUTO_VALUE_ON_ZEROの場合のみ有効となります(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」条件で新たに挿入された行を選択することもできます.すなわち、INSERT後すぐに「SELECT*FROM WHERE a IS NULL」を使用します.「a IS NULL」の条件を満たす行ではなく、新しく挿入された行が選択されます.しかし、上記のクエリをもう一度実行すると、「a IS NULL」の条件を満たすローに戻ります.aはプライマリ・キーなので、必ず空のセットに戻ります.変に見えますが、MySQLもそうしたくありませんが、ODBC規格にはこのような使い方があったので、ODBCをサポートするためにMySQLも仕方がないでしょう.ただしSQL_AUTO_IS_NULLを0にして禁止します.またAUTO_INCREMENTプロパティもコピーに迷惑をかけています.一般的にAUTO_をコピーINCREMENTプロパティは正しく動作しますが、以下の場合は問題があります.INSERT DELAYED ... VALES(LAST_INSERT_ID()は正しくコピーできません.ストアドプロシージャ挿入の使用AUTO_INCREMENT属性のレコードは正しくコピーできない.「ALTER TABLE」コマンドでAUTO_を追加INCREMENTプロパティの場合、プライマリスレーブノードで生成される値は異なる場合があります.この行はAUTO_INCREMENTプロパティの値は、物理的な格納順序に依存します.