mysql variables char_MySQL CHAR和VARCHAR数据类型介绍

CHAR类型是固定长度的，长度范围为0到255。如果存储的值不覆盖字段长度，存储数据的其他的部分会在值的右边自动添加空格。

VARCHAR字段中的值是可变长度的字符串，长度范围为0到65535。VARCHAR字段的值以1字节或2字节的长度前缀加上数据的方式存储。长度前缀指示了字段存储的字节数，如果一个字段存储的值不超过255字节，它使用一个长度前缀，如果字段存储的值超过了255字节，它使用两个长度前缀。

如果没有启用严格SQL模式，分配给CHAR或VARCHAR字段超过字段最大长度的值，这个值会被截断保存到数据库中，并会产生一个警告。对于VARCHAR字段来说，不管使用什么样的SQL模式，分配给VARCHAR字段超过字段最大长度的值，在值插入前，超过字段最大长度的部分会被截断，并会产生一个警告。对于CHAR字段来说，不管使用什么样的SQL模式，超过字段最大长度的部分会被截断，并隐式插入到数据库中。

--测试①

MariaDB [test]> show variables like '%sql%mode%';

+---------------+--------------------------------------------+

| Variable_name | Value                                      |

+---------------+--------------------------------------------+

| sql_mode      | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+---------------+--------------------------------------------+

1 row in set (0.26 sec)

MariaDB [test]> drop table vc;

Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));

Query OK, 0 rows affected (0.54 sec)

MariaDB [test]> INSERT INTO vc VALUES ('ab  ', 'ab  ');

Query OK, 1 row affected (0.14 sec)

--查看插入的数据中是否保留空格

MariaDB [test]> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;

+---------------------+---------------------+

| CONCAT('(', v, ')') | CONCAT('(', c, ')') |

+---------------------+---------------------+

| (ab  )              | (ab)                |

+---------------------+---------------------+

1 row in set (0.00 sec)

MariaDB [test]> select length(v),length(c) from vc;

+-----------+-----------+

| length(v) | length(c) |

+-----------+-----------+

|         4 |         2 |

+-----------+-----------+

1 row in set (0.00 sec)

--插入超过字段最大值的数据到表中

MariaDB [test]> insert into vc values('abcdefg','abcedeg');

Query OK, 1 row affected, 2 warnings (0.03 sec)

MariaDB [test]> show warnings;

+---------+------+----------------------------------------+

| Level   | Code | Message                                |

+---------+------+----------------------------------------+

| Warning | 1265 | Data truncated for column 'v' at row 1 |

| Warning | 1265 | Data truncated for column 'c' at row 1 |

+---------+------+----------------------------------------+

2 rows in set (0.00 sec)

MariaDB [test]> select * from vc;

+------+------+

| v    | c    |

+------+------+

| ab   | ab   |

| abcd | abce |

+------+------+

2 rows in set (0.00 sec)

--测试②

MariaDB [test]> CREATE TABLE names (myname CHAR(10));

Query OK, 0 rows affected (0.13 sec)

MariaDB [test]> INSERT INTO names VALUES ('Monty');

Query OK, 1 row affected (0.00 sec)

--查看表中数据在WHERE条件中使用=的条件中带空格的执行情况

MariaDB [test]> SELECT myname = 'Monty', myname = 'Monty  ' FROM names;

+------------------+--------------------+

| myname = 'Monty' | myname = 'Monty  ' |

+------------------+--------------------+

|                1 |                  1 |

+------------------+--------------------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names;

+--------+

| myname |

+--------+

| Monty  |

+--------+

1 row in set (0.00 sec)

MariaDB [test]> select length(myname) from names;

+----------------+

| length(myname) |

+----------------+

|              5 |

+----------------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname = 'Monty';

+--------+

| myname |

+--------+

| Monty  |

+--------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname = 'Monty ';

+--------+

| myname |

+--------+

| Monty  |

+--------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname = 'Monty  ';

+--------+

| myname |

+--------+

| Monty  |

+--------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname = 'Monty   ';

+--------+

| myname |

+--------+

| Monty  |

+--------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname = 'Monty    ';

+--------+

| myname |

+--------+

| Monty  |

+--------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname = 'Monty     ';

+--------+

| myname |

+--------+

| Monty  |

+--------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname = 'Monty      ';

+--------+

| myname |

+--------+

| Monty  |

+--------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname = 'Monty       ';

+--------+

| myname |

+--------+

| Monty  |

+--------+

1 row in set (0.00 sec)

--查看表中的数据在like条件下的执行情况

MariaDB [test]> SELECT myname LIKE 'Monty', myname LIKE 'Monty  ' FROM names;

+---------------------+-----------------------+

| myname LIKE 'Monty' | myname LIKE 'Monty  ' |

+---------------------+-----------------------+

|                   1 |                     0 |

+---------------------+-----------------------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname like '%Monty%';

+--------+

| myname |

+--------+

| Monty  |

+--------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname like 'Monty%';

+--------+

| myname |

+--------+

| Monty  |

+--------+

1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname like 'Monty %';

Empty set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname like 'Monty  %';

Empty set (0.00 sec)

MariaDB [test]> SELECT * FROM names where myname like 'Monty   %';

Empty set (0.00 sec)