mysql中int和varchar的长度详解

在mysql中一般人可能不会去问int与varchar区别了,这个估计一个数字类型,一个字符类型没有可比性了,同时他们长度也不一样,下面我来介绍一下mysql 中int和varchar的长度一些细节吧.

int:从 -2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) 的整型数据（所有数字）。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer

varcharL长度是0-255个字符.

mysql 字段中int后面所跟数字有何意义？varchar后的数字又有何意义？代码如下:

1. mysql>createtablet(aint(1));
2. QueryOK,0rowsaffected(0.10sec)
3. mysql>insertintotvalues(123);
4. QueryOK,1rowaffected(0.02sec)
6. mysql>insertintotvalues(12345678);
7. QueryOK,1rowaffected(0.03sec)
9. mysql>select*fromt;
10. +----------+
11. |a|
12. +----------+
13. |123|
14. |12345678|
15. +----------+

可见,int(1)并不表示一个字节,如果更长的数字会不会报错？代码如下:

1. mysql>insertintotvalues(1234567812345678);
2. ERROR1264(22003):Outofrangevalueforcolumn'a'atrow1
3. mysql>insertintotvalues(2147483648);
4. ERROR1264(22003):Outofrangevalueforcolumn'a'atrow1
5. mysql>insertintotvalues(2147483647);
6. QueryOK,1rowaffected(0.03sec)

int 型长度最大值是2^31 -1,加上有符号数,应该是四个字节的长度,代码如下:

1. mysql>altertabletaddcolumnbint;
2. QueryOK,5rowsaffected(0.25sec)
3. mysql>insertintotvalues(2147483647,2147483648);
4. ERROR1264(22003):Outofrangevalueforcolumn'b'atrow1
5. mysql>insertintotvalues(2147483647,2147483647);
6. QueryOK,1rowaffected(0.03sec)
8. mysql>select*fromt;
9. +------------+------------+
10. |a|b|
11. +------------+------------+
12. |123|NULL|
13. |12345678|NULL|
14. |65536|NULL|
15. |1073741824|NULL|
16. |2147483647|NULL|
17. |2147483647|2147483647|
18. +------------+------------+
19. 6rowsinset(0.00sec)

可见,int后是否跟数字与最大值没有关系,再看char 和varchar后的数字,代码如下:

1. mysql>altertabletaddcolumncchar(2);
2. QueryOK,6rowsaffected(0.17sec)
3. Records:6Duplicates:0Warnings:0
5. mysql>altertabletaddcolumndvarchar(2);
6. QueryOK,6rowsaffected(0.17sec)
7. Records:6Duplicates:0Warnings:0
9. mysql>desct;
10. +-------+------------+------+-----+---------+-------+
11. |Field|Type|Null|Key|Default|Extra|
12. +-------+------------+------+-----+---------+-------+
13. |a|int(1)|YES||NULL||
14. |b|int(11)|YES||NULL||
15. |c|char(2)|YES||NULL||
16. |d|varchar(2)|YES||NULL||
17. +-------+------------+------+-----+---------+-------+
18. 4rowsinset(0.01sec)
20. mysql>insertintotvalues(2147483647,2147483647,'abc','abc');
21. ERROR1406(22001):Datatoolongforcolumn'c'atrow1
22. mysql>insertintotvalues(2147483647,2147483647,'ab','abc');
23. ERROR1406(22001):Datatoolongforcolumn'd'atrow1
24. mysql>insertintotvalues(2147483647,2147483647,'ab','ab');
25. QueryOK,1rowaffected(0.02sec)

可见char和varchar后的数字是存储长度,那么对于中文字,会不会有字节数大于字数而溢出的问题呢？代码如下:

1. mysql>insertintotvalues(2147483647,2147483647,'ab','中化');
2. QueryOK,1rowaffected(0.02sec)
4. mysql>select*fromt;
5. +------------+------------+------+------+
6. |a|b|c|d|
7. +------------+------------+------+------+
8. |123|NULL|NULL|NULL|
9. |12345678|NULL|NULL|NULL|
10. |65536|NULL|NULL|NULL|
11. |1073741824|NULL|NULL|NULL|
12. |2147483647|NULL|NULL|NULL|
13. |2147483647|2147483647|NULL|NULL|
14. |2147483647|2147483647|ab|ab|
15. |2147483647|2147483647|ab|中化|
16. +------------+------------+------+------+
17. 8rowsinset(0.00sec)

中文字也是包含在2个字内的,代码如下:

1. mysql>insertintotvalues(2147483647,2147483647,'ab','中化a');
2. ERROR1406(22001):Datatoolongforcolumn'd'atrow1
3. mysql>insertintotvalues(2147483647,2147483647,'ab','?糸F');
4. QueryOK,1rowaffected(0.05sec)
6. mysql>select*fromt;
7. +------------+------------+------+------+
8. |a|b|c|d|
9. +------------+------------+------+------+
10. |123|NULL|NULL|NULL|
11. |12345678|NULL|NULL|NULL|
12. |65536|NULL|NULL|NULL|
13. |1073741824|NULL|NULL|NULL|
14. |2147483647|NULL|NULL|NULL|
15. |2147483647|2147483647|NULL|NULL|
16. |2147483647|2147483647|ab|ab|
17. |2147483647|2147483647|ab|中化|
18. |2147483647|2147483647|ab|?糸F|
19. +------------+------------+------+------+
20. 9rowsinset(0.01sec)

多一个字节都不行,看一下字符编码,mysql字符编码由服务器,数据库,表,字段四级组成,在windows下的mysql设置,代码如下:

1. mysql>showvariableslike"character%";
2. +--------------------------+--------------------------+
3. |Variable_name|Value|
4. +--------------------------+--------------------------+
5. |character_set_client|gbk|
6. |character_set_connection|gbk|
7. |character_set_database|utf8|
8. |character_set_filesystem|binary|
9. |character_set_results|gbk|
10. |character_set_server|utf8|
11. |character_set_system|utf8|
12. |character_sets_dir|D:mysqlsharecharsets|
13. +--------------------------+--------------------------+
14. 8rowsinset(0.00sec)//phpfensi.com
15. mysql>showcreatetablet;
16. t|CREATETABLE`t`(
17. `a`int(1)DEFAULTNULL,
18. `b`int(11)DEFAULTNULL,
19. `c`char(2)DEFAULTNULL,
20. `d`varchar(2)DEFAULTNULL
21. ENGINE=InnoDBDEFAULTCHARSET=utf8

linux下centos设置,代码如下:

1. mysql>showvariableslike"character%";
2. +--------------------------+----------------------------+
3. |Variable_name|Value|
4. +--------------------------+----------------------------+
5. |character_set_client|utf8|
6. |character_set_connection|utf8|
7. |character_set_database|utf8|
8. |character_set_filesystem|binary|
9. |character_set_results|utf8|
10. |character_set_server|utf8|
11. |character_set_system|utf8|
12. |character_sets_dir|/opt/mysql/share/charsets/|
13. +--------------------------+----------------------------+
14. 8rowsinset(0.00sec)
15. mysql>showfullfieldsfromt;
16. mysql>showcreatedatabasetest;
17. CREATEDATABASE`test`/*!40100DEFAULTCHARACTERSETutf8*/

字符编码是utf8,因此,对于utf8中文字符，varchar后面的数字,无论是中文还是英文,都表示相应的字数,不用担心截断.