MySQL char字符长度（length()方法查看）和char(10)指定的长度不一致

MySQL char字符长度（length()方法查看）和char(10)指定的长度不一致

建表（utf8和ascii的2张表）

mysql> create table zyl_tmp_20210701_utf8(City CHAR(10), Street VARCHAR(10)) CHARSET=utf8;
Query OK, 0 rows affected (0.15 sec)

mysql> create table zyl_tmp_20210701_ascii(City CHAR(10), Street VARCHAR(10)) CHARSET=ascii;
Query OK, 0 rows affected (0.11 sec)

查看建表语句

mysql> show create table zyl_tmp_20210701_ascii;
+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                  | Create Table                                                                                                                                      |
+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| zyl_tmp_20210701_ascii | CREATE TABLE `zyl_tmp_20210701_ascii` (
  `City` char(10) DEFAULT NULL,
  `Street` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=ascii |
+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table zyl_tmp_20210701_utf8;
+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                 | Create Table                                                                                                                                    |
+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| zyl_tmp_20210701_utf8 | CREATE TABLE `zyl_tmp_20210701_utf8` (
  `City` char(10) DEFAULT NULL,
  `Street` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

插入数据

mysql> INSERT INTO zyl_tmp_20210701_utf8 values('Pune', 'Oxford');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO zyl_tmp_20210701_ascii values('Pune', 'Oxford');
Query OK, 1 row affected (0.00 sec)

查看数据

mysql> select * from zyl_tmp_20210701_ascii;
+------+--------+
| City | Street |
+------+--------+
| Pune | Oxford |
+------+--------+
1 row in set (0.00 sec)

mysql> select * from zyl_tmp_20210701_utf8;
+------+--------+
| City | Street |
+------+--------+
| Pune | Oxford |
+------+--------+
1 row in set (0.00 sec)

mysql> select concat("'", City, "'") from zyl_tmp_20210701_utf8;
+------------------------+
| concat("'", City, "'") |
+------------------------+
| 'Pune'                 |
+------------------------+
1 row in set (0.00 sec)

查询长度

mysql> SELECT LENGTH(City), LENGTH(Street) FROM zyl_tmp_20210701_utf8;
+--------------+----------------+
| LENGTH(City) | LENGTH(Street) |
+--------------+----------------+
|            4 |              6 |
+--------------+----------------+
1 row in set (0.02 sec)

mysql> SELECT LENGTH(City), LENGTH(Street) FROM zyl_tmp_20210701_ascii;
+--------------+----------------+
| LENGTH(City) | LENGTH(Street) |
+--------------+----------------+
|            4 |              6 |
+--------------+----------------+
1 row in set (0.00 sec)

插入后缀为空字符串的数据

mysql> INSERT INTO zyl_tmp_20210701_ascii values('Xune   ', 'Oxford   ');
Query OK, 1 row affected (0.00 sec)

查询数据

mysql> select concat("'", City, "'") from zyl_tmp_20210701_utf8;
+------------------------+
| concat("'", City, "'") |
+------------------------+
| 'Pune'                 |
+------------------------+
1 row in set (0.00 sec)

mysql> select concat("'", City, "'") from zyl_tmp_20210701_ascii;
+------------------------+
| concat("'", City, "'") |
+------------------------+
| 'Pune'                 |
| 'Xune'                 |
+------------------------+
2 rows in set (0.00 sec)

查询长度

mysql> SELECT LENGTH(City), LENGTH(Street) FROM zyl_tmp_20210701_ascii;
+--------------+----------------+
| LENGTH(City) | LENGTH(Street) |
+--------------+----------------+
|            4 |              6 |
|            4 |              9 |
+--------------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT LENGTH(City), LENGTH(Street), CHAR_LENGTH(City), CHAR_LENGTH(Street) FROM zyl_tmp_20210701_ascii;
+--------------+----------------+-------------------+---------------------+
| LENGTH(City) | LENGTH(Street) | CHAR_LENGTH(City) | CHAR_LENGTH(Street) |
+--------------+----------------+-------------------+---------------------+
|            4 |              6 |                 4 |                   6 |
|            4 |              9 |                 4 |                   9 |
+--------------+----------------+-------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT LENGTH(City), LENGTH(Street), CHAR_LENGTH(City), CHAR_LENGTH(Street) FROM zyl_tmp_20210701_utf8;
+--------------+----------------+-------------------+---------------------+
| LENGTH(City) | LENGTH(Street) | CHAR_LENGTH(City) | CHAR_LENGTH(Street) |
+--------------+----------------+-------------------+---------------------+
|            4 |              6 |                 4 |                   6 |
+--------------+----------------+-------------------+---------------------+
1 row in set (0.00 sec)

总结：

“CHAR检索时会从列中删除尾随空格。” CHAR后面有空格的话（char(10)类型存入‘abc’、‘abc ’后面都会有7个空格），实际存储的 和 查询出的不一样。

参考：

https://dev.mysql.com/doc/refman/5.6/en/char.html