测试数据
Oracle 11.2测试表：
create table white_space (
id int,
name varchar2(128),
name2 char(128)
);
MySQL 5.7、TiDB 3.0测试表：
drop table white_space;
create table white_space (
id bigint(20) unsigned not null auto_increment,
name varchar(128),
name2 char(128),
primary key (id)
) default charset=utf8mb4;
分别在不同类型数据库中插入测试数据：
INSERT INTO white_space (name,name2) VALUES ('Albert','Albert');
INSERT INTO white_space (name,name2) VALUES ('Albert ','Albert '); -- 后面一个空格
INSERT INTO white_space (name,name2) VALUES ('Albert ','Albert '); -- 后面两个空格
INSERT INTO white_space (name,name2) VALUES ('Albert ','Albert '); -- 后面三个空格
INSERT INTO white_space (name,name2) VALUES (' Albert',' Albert'); -- 前面一个空格
INSERT INTO white_space (name,name2) VALUES (' Albert',' Albert'); -- 前面两个空格
INSERT INTO white_space (name,name2) VALUES (' Albert',' Albert'); -- 前面三个空格
INSERT INTO white_space (name,name2) VALUES ('A lb er t','A lb er t'); -- 中间分别有1,2,3个空格
--oracle的测试数据
INSERT INTO white_space (id,name,name2) VALUES (1,'Albert','Albert');
INSERT INTO white_space (id,name,name2) VALUES (2,'Albert ','Albert '); -- 后面一个空格
INSERT INTO white_space (id,name,name2) VALUES (3,'Albert ','Albert '); -- 后面两个空格
INSERT INTO white_space (id,name,name2) VALUES (4,'Albert ','Albert '); -- 后面三个空格
INSERT INTO white_space (id,name,name2) VALUES (5,' Albert',' Albert'); -- 前面一个空格
INSERT INTO white_space (id,name,name2) VALUES (6,' Albert',' Albert'); -- 前面两个空格
INSERT INTO white_space (id,name,name2) VALUES (7,' Albert',' Albert'); -- 前面三个空格
INSERT INTO white_space (id,name,name2) VALUES (8,'A lb er t','A lb er t'); -- 中间分别有1,2,3个空格
检查数据
--mysql\tidb
select id,name,length(name),char_length(name),name2,length(name2),char_length(name2) from white_space;
+----+--------------+--------------+-------------------+--------------+---------------+--------------------+
| id | name | length(name) | char_length(name) | name2 | length(name2) | char_length(name2) |
+----+--------------+--------------+-------------------+--------------+---------------+--------------------+
| 1 | Albert | 6 | 6 | Albert | 6 | 6 |
| 2 | Albert | 7 | 7 | Albert | 6 | 6 |
| 3 | Albert | 8 | 8 | Albert | 6 | 6 |
| 4 | Albert | 9 | 9 | Albert | 6 | 6 |
| 5 | Albert | 7 | 7 | Albert | 7 | 7 |
| 6 | Albert | 8 | 8 | Albert | 8 | 8 |
| 7 | Albert | 9 | 9 | Albert | 9 | 9 |
| 8 | A lb er t | 12 | 12 | A lb er t | 12 | 12 |
+----+--------------+--------------+-------------------+--------------+---------------+--------------------+
--oracle，因为char类型按照定义长度在后面补空格了，所以长度一致
set linesize 400
set long 999
col name for a20
col length(name) for 999
col lengthb(name) for 999
col name2 for a20
col length(name2) for 999
col lengthb(name2) for 999
select name,length(name),lengthb(name),name2,length(name2),lengthb(name2) from scott.white_space;
NAME LEN LEN NAME2 LEN LEN
-------------------- --- --- -------------------- --- ---
Albert 6 6 Albert 128 128
Albert 7 7 Albert 128 128
Albert 8 8 Albert 128 128
Albert 9 9 Albert 128 128
Albert 7 7 Albert 128 128
Albert 8 8 Albert 128 128
Albert 9 9 Albert 128 128
A lb er t 12 12 A lb er t 128 128
8 rows selected
查询数据
--mysql
select * from white_space where name ='Albert';
select * from white_space where name ='Albert ';
select * from white_space where name ='Albert ';
select * from white_space where name2 ='Albert';
select * from white_space where name2 ='Albert ';
select * from white_space where name2 ='Albert ';
--以上语句查询结果都一样:
+----+-----------+--------+
| id | name | name2 |
+----+-----------+--------+
| 1 | Albert | Albert |
| 2 | Albert | Albert |
| 3 | Albert | Albert |
| 4 | Albert | Albert |
+----+-----------+--------+
--如果前面或中间有空格则查询时不会被忽略掉
select * from white_space where name =' Albert';
+----+---------+---------+
| id | name | name2 |
+----+---------+---------+
| 5 | Albert | Albert |
+----+---------+---------+
select * from white_space where name =' Albert';
+----+----------+----------+
| id | name | name2 |
+----+----------+----------+
| 6 | Albert | Albert |
+----+----------+----------+
select * from white_space where name =' Albert';
+----+-----------+-----------+
| id | name | name2 |
+----+-----------+-----------+
| 7 | Albert | Albert |
+----+-----------+-----------+
--tidb的情况
-- varchar类型不会忽略后面的空格
select * from white_space where name ='Albert';
+----+--------+--------+
| id | name | name2 |
+----+--------+--------+
| 1 | Albert | Albert |
+----+--------+--------+
1 row in set (0.00 sec)
select * from white_space where name ='Albert ';
+----+---------+--------+
| id | name | name2 |
+----+---------+--------+
| 2 | Albert | Albert |
+----+---------+--------+
1 row in set (0.01 sec)
select * from white_space where name ='Albert ';
+----+----------+--------+
| id | name | name2 |
+----+----------+--------+
| 3 | Albert | Albert |
+----+----------+--------+
1 row in set (0.00 sec)
-- char类型因为存储数据时丢失了后面的空格，所以结尾带空格的查询不到
select * from white_space where name2 ='Albert';
+----+-----------+--------+
| id | name | name2 |
+----+-----------+--------+
| 1 | Albert | Albert |
| 2 | Albert | Albert |
| 3 | Albert | Albert |
| 4 | Albert | Albert |
+----+-----------+--------+
4 rows in set (0.01 sec)
select * from white_space where name2 ='Albert ';
Empty set (0.01 sec)
select * from white_space where name2 ='Albert ';
Empty set (0.00 sec)
--varchar和char类型 前面或中间有空格时查询时严格匹配所有字符
select * from white_space where name =' Albert';
+----+---------+---------+
| id | name | name2 |
+----+---------+---------+
| 5 | Albert | Albert |
+----+---------+---------+
1 row in set (0.01 sec)
select * from white_space where name =' Albert';
+----+----------+----------+
| id | name | name2 |
+----+----------+----------+
| 6 | Albert | Albert |
+----+----------+----------+
1 row in set (0.00 sec)
select * from white_space where name =' Albert';
+----+-----------+-----------+
| id | name | name2 |
+----+-----------+-----------+
| 7 | Albert | Albert |
+----+-----------+-----------+
1 row in set (0.00 sec)
--oracle的情况
--varchar类型严格匹配字符
10:51:18 SCOTT@orcl11g>select * from white_space where name ='Albert';
ID NAME NAME2
---------- -------------------- --------------------
1 Albert Albert
10:51:21 SCOTT@orcl11g>select * from white_space where name ='Albert ';
ID NAME NAME2
---------- -------------------- --------------------
2 Albert Albert
10:51:21 SCOTT@orcl11g>select * from white_space where name ='Albert ';
ID NAME NAME2
---------- -------------------- --------------------
3 Albert Albert
--char类型会忽略结尾的空格
10:51:21 SCOTT@orcl11g>select * from white_space where name2 ='Albert';
ID NAME NAME2
---------- -------------------- --------------------
1 Albert Albert
2 Albert Albert
3 Albert Albert
4 Albert Albert
10:51:21 SCOTT@orcl11g>select * from white_space where name2 ='Albert ';
ID NAME NAME2
---------- -------------------- --------------------
1 Albert Albert
2 Albert Albert
3 Albert Albert
4 Albert Albert
10:51:21 SCOTT@orcl11g>select * from white_space where name2 ='Albert ';
ID NAME NAME2
---------- -------------------- --------------------
1 Albert Albert
2 Albert Albert
3 Albert Albert
4 Albert Albert
--varchar和char类型和mysql/tidb一样，不会忽略前面或中间的任何空格
0:53:47 SCOTT@orcl11g>select * from white_space where name =' Albert';
ID NAME NAME2
---------- -------------------- --------------------
5 Albert Albert
10:53:50 SCOTT@orcl11g>select * from white_space where name =' Albert';
ID NAME NAME2
---------- -------------------- --------------------
6 Albert Albert
10:53:50 SCOTT@orcl11g>select * from white_space where name =' Albert';
ID NAME NAME2
---------- -------------------- --------------------
7 Albert Albert
10:53:50 SCOTT@orcl11g>select * from white_space where name2 =' Albert';
ID NAME NAME2
---------- -------------------- --------------------
5 Albert Albert
10:54:25 SCOTT@orcl11g>select * from white_space where name2 =' Albert';
ID NAME NAME2
---------- -------------------- --------------------
6 Albert Albert
10:54:25 SCOTT@orcl11g>select * from white_space where name2 =' Albert';
ID NAME NAME2
---------- -------------------- --------------------
7 Albert Albert
总结：
空格行为总结 | mysql | tidb | oracle |
varchar类型空格的存储 | 保留所有空格，不进行任何删除 | 保留所有空格，不进行任何删除 | 保留所有空格，不进行任何删除 |
char类型空格的存储 | char在存储的时候会将右侧空格进行剔除，保留左侧和中间空格 | char在存储的时候会将右侧空格进行剔除，保留左侧和中间空格 | 保留前面和中间所有空格，若不够字段定义长度，则后面补空格 |
varchar类型查询 | 右侧末尾的空格会忽略 | 不忽略任何空格 | 不忽略任何空格 |
char类型查询 | 右侧末尾的空格会忽略 | 不忽略任何空格，因为删除了结尾空格，可能会查不到数据 | 右侧末尾的空格会忽略 |
应用代码容易踩坑的地方：使用char类型时，mysql/tidb丢失了右侧末尾的空格。Oracle则在后面可能会多出来空格，会有多余空格异常。
mysql varchar类型查询的时候右侧末尾的空格会被忽略，可以使用where name = binary('Albert ')方式来避免。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
