实例是最好的说明,所以,废话少说,看表看例子~
mysql> show create table test_varchar_utf8\G
*************************** 1. row ***************************
Table: test_varchar_utf8
Create Table: CREATE TABLE `test_varchar_utf8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(12) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
#这里name字段数据类型为varchar长度指定为12,这个12究竟是字节数还是字符数呢?
#尝试插入几组不同长度的值
mysql> insert into test_varchar_utf8(name)values('12345678901');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_varchar_utf8(name)values('123456789012');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_varchar_utf8(name)values('1234567890123');
ERROR 1406 (22001): Data too long for column 'name' at row 1
#发现,对于非中文字符串,可以插入包含12个字符以及小于12个字符的字符串,当大于12个字符时报错。
#因为对于非中文的字符一个字符占用一个字节,所以,至此还不能说明varchar(n)中的n究竟代表字节数还是字符数
#试着插入另外一组值包含中文字符的字符串
mysql> insert into test_varchar_utf8(name)values('你');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_varchar_utf8(name)values('你好');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_varchar_utf8(name)values('你好么');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_varchar_utf8(name)values('你好么亲');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_varchar_utf8(name)values('你好么亲爱的');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_varchar_utf8(name)values('你好么亲爱的你好么亲爱的');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_varchar_utf8(name)values('你好么亲爱的你好么亲爱的你');
ERROR 1406 (22001): Data too long for column 'name' at row 1
#发现,对于中文字符串,也可以插入包含12个字符以及小于12个字符的字符串,当大于12个字符时报错。
#因为对于中文的字符一个字符占用三个字节,所以,说明varchar(n)中的n代表字符数而非字节数。
#对于char类型,可以做类似的实验,结论相同
mysql> show create table test_char_utf8\G
*************************** 1. row ***************************
Table: test_char_utf8
Create Table: CREATE TABLE `test_char_utf8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(12) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into test_char_utf8(name)values('12345678901');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_char_utf8(name)values('123456789012');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_char_utf8(name)values('1234567890123');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into test_char_utf8(name)values('你好');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_char_utf8(name)values('你好么');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_char_utf8(name)values('你好么亲');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_char_utf8(name)values('你好么亲爱的');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_char_utf8(name)values('你好么亲爱的你好么亲爱的');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_char_utf8(name)values('你好么亲爱的你好么亲爱的你');
ERROR 1406 (22001): Data too long for column 'name' at row 1
#下面的查询显示了utf8字符集下中文字符与非中文字符所占用的字节数对比
mysql> select id,name,length(name),char_length(name) from test_varchar_utf8;
+----+--------------------------------------+--------------+-------------------+
| id | name | length(name) | char_length(name) |
+----+--------------------------------------+--------------+-------------------+
| 1 | 12345678901 | 11 | 11 |
| 2 | 123456789012 | 12 | 12 |
| 3 | 你 | 3 | 1 |
| 4 | 你好 | 6 | 2 |
| 5 | 你好么 | 9 | 3 |
| 6 | 你好么亲 | 12 | 4 |
| 7 | 你好么亲爱的 | 18 | 6 |
| 8 | 你好么亲爱的你好么亲爱的 | 36 | 12 |
+----+--------------------------------------+--------------+-------------------+
8 rows in set (0.00 sec)
mysql> select id,name,length(name),char_length(name) from test_char_utf8;
+----+--------------------------------------+--------------+-------------------+
| id | name | length(name) | char_length(name) |
+----+--------------------------------------+--------------+-------------------+
| 1 | 12345678901 | 11 | 11 |
| 2 | 123456789012 | 12 | 12 |
| 3 | 你好 | 6 | 2 |
| 4 | 你好么 | 9 | 3 |
| 5 | 你好么亲 | 12 | 4 |
| 6 | 你好么亲爱的 | 18 | 6 |
| 7 | 你好么亲爱的你好么亲爱的 | 36 | 12 |
+----+--------------------------------------+--------------+-------------------+
7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
如果你把例子从头到尾看了下来,那么你已经深刻理解了,也就无需我在做什么总结了。是的你对了,(n)表示字符数