MySQL数据类型char与varchar中数字代表的究竟是字节数还是字符数？

待我称王封你为后i 2022-09-21 04:56 119阅读 0赞

**实例是最好的说明，所以,废话少说，看表看例子~**

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)

如果你把例子从头到尾看了下来，那么你已经深刻理解了，也就无需我在做什么总结了。是的你对了，(n)表示字符数！