MYSQL中 char 和 varchar的区别

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)
 
mysql> INSERT INTO vc VALUES ('ab ', 'ab ');
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab )    | (ab)    |
+---------------------+---------------------+
1 row in set (0.06 sec)<br><br><br>

mysql> show create table vc;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| vc | CREATE TABLE `vc` (
 `v` varchar(4) DEFAULT NULL,
 `c` char(4) DEFAULT NULL,
 UNIQUE KEY `v_UNIQUE` (`v`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vc where v='    ab';
Empty set (0.00 sec)
 
mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vc where c='     ab';
Empty set (0.00 sec)

mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vc where v='ab    ';
+--------------------+--------------------+
| concat('(',v, ')') | concat('(',c, ')') |
+--------------------+--------------------+
| (ab )    | (ab)    |
+--------------------+--------------------+
1 row in set (0.00 sec)
 
mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vc where c='ab    ';
+--------------------+--------------------+
| concat('(',v, ')') | concat('(',c, ')') |
+--------------------+--------------------+
| (ab )    | (ab)    |
+--------------------+--------------------+
1 row in set (0.00 sec)<br><br><br>在所有字符串的比较中，不论是varchar，text,char 都会忽略去掉尾部空格，除了like子句，like子句不会去掉尾部空格如下所示<br><br>

mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vipshop_dba.vc where c like 'ab'; （无尾部空格）
+--------------------+--------------------+
| concat('(',v, ')') | concat('(',c, ')') |
+--------------------+--------------------+
| (ab ) | (ab) |
| ( ab ) | (ab) |
+--------------------+--------------------+
2 rows in set (0.00 sec)
 
mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vipshop_dba.vc where c like 'ab '; （有尾部空格）
Empty set (0.00 sec)
 
 
 
CHAR在储存时会填充空格，在检索时尾部空格会去掉，无论这个尾部空格是自动填充的还是数据本身的。这个去掉尾部空格的动作在比较之前会发生
 
 
 
mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vipshop_dba.vc where v like 'ab '; (有尾部空格)
+--------------------+--------------------+
| concat('(',v, ')') | concat('(',c, ')') |
+--------------------+--------------------+
| (ab ) | (ab) |
+--------------------+--------------------+
1 row in set (0.01 sec)
 
mysql> SELECT concat('(',v, ')'),concat('(',c, ')') FROM vipshop_dba.vc where v like 'ab'; （无尾部空格）
Empty set (0.00 sec)

mysql> insert into vc (v,c)values('ab  ', 'ab  ')
 -> ;
ERROR 1062 (23000): Duplicate entry 'ab ' for key 'v_UNIQUE'
mysql> insert into vc (v,c)values(' ab  ', 'ab  ')
 -> ;
Query OK, 1 row affected, 1 warning (0.00 sec)<br>说明虽然varchar 尾部空格可以保留，但是索引上似乎额外做了限制。如果varchar是唯一索引，插入的值区别只在于尾部空格的数量的话则会报 Duplicate key