mysql 中int和varchar的長度

mysql 欄位中int後面所跟數字有何意義？ varchar後的數字又有何意義？

mysql> create table t(a int(1));Query OK, 0 rows affected (0.10 sec)mysql> insert into t values(123);Query OK, 1 row affected (0.02 sec)mysql> insert into t values(12345678);Query OK, 1 row affected (0.03 sec)mysql> select * from t;+----------+| a        |+----------+|      123 || 12345678 |+----------+

可見，int(1)並不表示一個位元組。
如果更長的數字會不會報錯？

mysql> insert into t values(1234567812345678);ERROR 1264 (22003): Out of range value for column 'a' at row 1mysql> insert into t values(2147483648);ERROR 1264 (22003): Out of range value for column 'a' at row 1mysql> insert into t values(2147483647);Query OK, 1 row affected (0.03 sec)

int 型長度最大值是2^31 -1 ，加上有符號數，應該是四個位元組的長度。

mysql> alter table t add column b int;Query OK, 5 rows affected (0.25 sec)mysql> insert into t values(2147483647,2147483648);ERROR 1264 (22003): Out of range value for column 'b' at row 1mysql> insert into t values(2147483647,2147483647);Query OK, 1 row affected (0.03 sec)mysql> select * from t;+------------+------------+| a          | b          |+------------+------------+|        123 |       NULL ||   12345678 |       NULL ||      65536 |       NULL || 1073741824 |       NULL || 2147483647 |       NULL || 2147483647 | 2147483647 |+------------+------------+6 rows in set (0.00 sec)

可見，int後是否跟數字與最大值沒有關係。

再看char 和varchar後的數字

mysql> alter table t add column c char(2);Query OK, 6 rows affected (0.17 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> alter table t add column d varchar(2);Query OK, 6 rows affected (0.17 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> desc t;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| a     | int(1)     | YES  |     | NULL    |       || b     | int(11)    | YES  |     | NULL    |       || c     | char(2)    | YES  |     | NULL    |       || d     | varchar(2) | YES  |     | NULL    |       |+-------+------------+------+-----+---------+-------+4 rows in set (0.01 sec)mysql> insert into t values(2147483647,2147483647,'abc','abc');ERROR 1406 (22001): Data too long for column 'c' at row 1mysql> insert into t values(2147483647,2147483647,'ab','abc');ERROR 1406 (22001): Data too long for column 'd' at row 1mysql> insert into t values(2147483647,2147483647,'ab','ab');Query OK, 1 row affected (0.02 sec)

可見char和varchar後的數字是儲存長度。
那麼對於中文字，會不會有位元組數大於字數而溢出的問題呢？

mysql> insert into t values(2147483647,2147483647,'ab','中化');Query OK, 1 row affected (0.02 sec)mysql> select * from t;+------------+------------+------+------+| a          | b          | c    | d    |+------------+------------+------+------+|        123 |       NULL | NULL | NULL ||   12345678 |       NULL | NULL | NULL ||      65536 |       NULL | NULL | NULL || 1073741824 |       NULL | NULL | NULL || 2147483647 |       NULL | NULL | NULL || 2147483647 | 2147483647 | NULL | NULL || 2147483647 | 2147483647 | ab   | ab   || 2147483647 | 2147483647 | ab   | 中化 |+------------+------------+------+------+8 rows in set (0.00 sec)

中文字也是包含在2個字內的。

mysql> insert into t values(2147483647,2147483647,'ab','中化a');ERROR 1406 (22001): Data too long for column 'd' at row 1mysql> insert into t values(2147483647,2147483647,'ab','烎鎔');Query OK, 1 row affected (0.05 sec)mysql> select * from t;+------------+------------+------+------+| a          | b          | c    | d    |+------------+------------+------+------+|        123 |       NULL | NULL | NULL ||   12345678 |       NULL | NULL | NULL ||      65536 |       NULL | NULL | NULL || 1073741824 |       NULL | NULL | NULL || 2147483647 |       NULL | NULL | NULL || 2147483647 | 2147483647 | NULL | NULL || 2147483647 | 2147483647 | ab   | ab   || 2147483647 | 2147483647 | ab   | 中化 || 2147483647 | 2147483647 | ab   | 烎鎔 |+------------+------------+------+------+9 rows in set (0.01 sec)

多一個位元組都不行。
看一下字元編碼。mysql字元編碼由伺服器，資料庫，表，欄位四級組成。
在windows下的mysql設定：

mysql> show variables like "character%";+--------------------------+--------------------------+| Variable_name            | Value                    |+--------------------------+--------------------------+| character_set_client     | gbk                      || character_set_connection | gbk                      || character_set_database   | utf8                     || character_set_filesystem | binary                   || character_set_results    | gbk                      || character_set_server     | utf8                     || character_set_system     | utf8                     || character_sets_dir       | D:\mysql\share\charsets\ |+--------------------------+--------------------------+8 rows in set (0.00 sec)mysql> show create table t; t     | CREATE TABLE `t` ( `a` int(1) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` char(2) DEFAULT NULL, `d` varchar(2) DEFAULT NULL ENGINE=InnoDB DEFAULT CHARSET=utf8

linux下centos設定：

mysql> show variables like "character%";+--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | utf8                       || character_set_connection | utf8                       || character_set_database   | utf8                       || character_set_filesystem | binary                     || character_set_results    | utf8                       || character_set_server     | utf8                       || character_set_system     | utf8                       || character_sets_dir       | /opt/mysql/share/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)mysql> show full fields from t;mysql> show create database test;CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */

字元編碼是utf8，
因此，對於utf8中文字元，varchar後面的數字，無論是中文還是英文，都表示相應的字數，不用擔心截斷。

http://abloz.com/2011/09/19/mysql-in-int-and-varchar-length.html