结论
- 对于char、varchar、text 等字符串类型数据，进行相等判断时，尾部空格不参与。也就是
'z'与'z '是相等的。 - char 类型数据在存储时，会在尾部填充空格至该字段声明的长度；查询时会去掉尾部空格，具体见 MySQL：CHAR类型 。varchar、text 不会这么做。
- 如果要精确匹配varchar、text 的尾部空格，可以用 like、长度判断等方式。
示例: 证明 char 会去掉尾部空格
建表：
use test;
CREATE TABLE `user_info` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`c1` VARCHAR(6) NOT NULL,
`c2` CHAR(6) NOT NULL,
`c3` TEXT,
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
插入数据：
INSERT INTO user_info(c1, c2, c3) values
('x', 'x', 'x'),
('x ', 'x ', 'x ')
;
查询长度：
mysql root@127.0.0.1:test> select c1, length(c1), c2, length(c2), c3, length(c3) from user_info
+------+------------+----+------------+------+------------+
| c1 | length(c1) | c2 | length(c2) | c3 | length(c3) |
+------+------------+----+------------+------+------------+
| x | 1 | x | 1 | x | 1 |
| x | 4 | x | 1 | x | 4 |
+------+------------+----+------------+------+------------+
示例: 尾部空格的判等
use test;
CREATE TABLE `user_info` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`c1` VARCHAR(6) NOT NULL,
`c2` CHAR(6) NOT NULL,
`c3` TEXT,
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
插入数据:
INSERT INTO user_info(c1, c2, c3) values
('x', 'x', 'x'),
('x ', 'x ', 'x '),
(' x ', ' x ', ' x ');
查询所有数据：
mysql root@127.0.0.1:test> select * from user_info;
+----+-----+----+-----+
| id | c1 | c2 | c3 |
+----+-----+----+-----+
| 1 | x | x | x |
| 2 | x | x | x |
| 3 | x | x | x |
+----+-----+----+-----+
对 varchar 类型的 c1 列进行查询：
mysql root@127.0.0.1:test> select * from user_info where c1 = 'x'
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
| 1 | x | x | x |
| 2 | x | x | x |
+----+----+----+----+
mysql root@127.0.0.1:test> select * from user_info where c1 = 'x '
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
| 1 | x | x | x |
| 2 | x | x | x |
+----+----+----+----+
2 rows in set
Time: 0.007s
mysql root@127.0.0.1:test> select * from user_info where c1 = 'x '
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
| 1 | x | x | x |
| 2 | x | x | x |
+----+----+----+----+
mysql root@127.0.0.1:test> select * from user_info where c1 = ' x '
+----+-----+----+-----+
| id | c1 | c2 | c3 |
+----+-----+----+-----+
| 3 | x | x | x |
+----+-----+----+-----+
mysql root@127.0.0.1:test> select * from user_info where c1 = ' x'
+----+-----+----+-----+
| id | c1 | c2 | c3 |
+----+-----+----+-----+
| 3 | x | x | x |
+----+-----+----+-----+
对 char 类型的 c2 列进行查询：
mysql root@127.0.0.1:test> select * from user_info where c2 = 'x'
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
| 1 | x | x | x |
| 2 | x | x | x |
+----+----+----+----+
mysql root@127.0.0.1:test> select * from user_info where c2 = 'x '
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
| 1 | x | x | x |
| 2 | x | x | x |
+----+----+----+----+
mysql root@127.0.0.1:test> select * from user_info where c2 = 'x '
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
| 1 | x | x | x |
| 2 | x | x | x |
+----+----+----+----+
mysql root@127.0.0.1:test> select * from user_info where c2 = ' x '
+----+-----+----+-----+
| id | c1 | c2 | c3 |
+----+-----+----+-----+
| 3 | x | x | x |
+----+-----+----+-----+
mysql root@127.0.0.1:test> select * from user_info where c2 = ' x'
+----+-----+----+-----+
| id | c1 | c2 | c3 |
+----+-----+----+-----+
| 3 | x | x | x |
+----+-----+----+-----+
对 text 类型的 c3 列进行查询：
mysql root@127.0.0.1:test> select * from user_info where c3 = 'x'
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
| 1 | x | x | x |
| 2 | x | x | x |
+----+----+----+----+
mysql root@127.0.0.1:test> select * from user_info where c3 = 'x '
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
| 1 | x | x | x |
| 2 | x | x | x |
+----+----+----+----+
mysql root@127.0.0.1:test> select * from user_info where c3 = 'x '
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
| 1 | x | x | x |
| 2 | x | x | x |
+----+----+----+----+
mysql root@127.0.0.1:test> select * from user_info where c3 = ' x '
+----+-----+----+-----+
| id | c1 | c2 | c3 |
+----+-----+----+-----+
| 3 | x | x | x |
+----+-----+----+-----+
mysql root@127.0.0.1:test> select * from user_info where c3 = ' x '
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
mysql root@127.0.0.1:test> select * from user_info where c3 = ' x'
+----+-----+----+-----+
| id | c1 | c2 | c3 |
+----+-----+----+-----+
| 3 | x | x | x |
+----+-----+----+-----+
示例: 精确匹配尾部空格
建表：
use test;
CREATE TABLE `user_info` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`c1` VARCHAR(6) NOT NULL,
`c2` CHAR(6) NOT NULL,
`c3` TEXT,
PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
插入数据：
INSERT INTO user_info(c1, c2, c3) values
('x', 'x', 'x'),
('x ', 'x ', 'x ')
;
对于 varchar：
mysql root@127.0.0.1:test> select * from user_info where c1 like 'x'
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
| 1 | x | x | x |
+----+----+----+----+
mysql root@127.0.0.1:test> select * from user_info where c1 like 'x '
+----+----+----+----+
| id | c1 | c2 | c3 |
+----+----+----+----+
mysql root@127.0.0.1:test> select * from user_info where c1 like 'x '
+----+------+----+------+
| id | c1 | c2 | c3 |
+----+------+----+------+
| 2 | x | x | x |
+----+------+----+------+
mysql root@127.0.0.1:test> select * from user_info where c1 = 'x '
+----+------+----+------+
| id | c1 | c2 | c3 |
+----+------+----+------+
| 1 | x | x | x |
| 2 | x | x | x |
+----+------+----+------+
mysql root@127.0.0.1:test> select * from user_info where c1 = 'x ' and length(c1) = 4
+----+------+----+------+
| id | c1 | c2 | c3 |
+----+------+----+------+
| 2 | x | x | x |
+----+------+----+------+