CHAR類型是固定長(zhǎng)度的，長(zhǎng)度范圍為0到255。如果存儲(chǔ)的值不覆蓋字段長(zhǎng)度，存儲(chǔ)數(shù)據(jù)的其他的部分會(huì)在值的右邊自動(dòng)添加空格。

VARCHAR字段中的值是可變長(zhǎng)度的字符串，長(zhǎng)度范圍為0到65535。VARCHAR字段的值以1字節(jié)或2字節(jié)的長(zhǎng)度前綴加上數(shù)據(jù)的方式存儲(chǔ)。長(zhǎng)度前綴指示了字段存儲(chǔ)的字節(jié)數(shù)，如果一個(gè)字段存儲(chǔ)的值不超過(guò)255字節(jié)，它使用一個(gè)長(zhǎng)度前綴，如果字段存儲(chǔ)的值超過(guò)了255字節(jié)，它使用兩個(gè)長(zhǎng)度前綴。

如果沒(méi)有啟用嚴(yán)格SQL模式，分配給CHAR或VARCHAR字段超過(guò)字段最大長(zhǎng)度的值，這個(gè)值會(huì)被截?cái)啾４娴綌?shù)據(jù)庫(kù)中，并會(huì)產(chǎn)生一個(gè)警告。對(duì)于VARCHAR字段來(lái)說(shuō)，不管使用什么樣的SQL模式，分配給VARCHAR字段超過(guò)字段最大長(zhǎng)度的值，在值插入前，超過(guò)字段最大長(zhǎng)度的部分會(huì)被截?cái)?，并?huì)產(chǎn)生一個(gè)警告。對(duì)于CHAR字段來(lái)說(shuō)，不管使用什么樣的SQL模式，超過(guò)字段最大長(zhǎng)度的部分會(huì)被截?cái)啵㈦[式插入到數(shù)據(jù)庫(kù)中。

--測(cè)試①

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)

--查看插入的數(shù)據(jù)中是否保留空格

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)

--插入超過(guò)字段最大值的數(shù)據(jù)到表中

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)
--測(cè)試②

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)

--查看表中數(shù)據(jù)在WHERE條件中使用=的條件中帶空格的執(zhí)行情況

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)

--查看表中的數(shù)據(jù)在like條件下的執(zhí)行情況

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)
網(wǎng)站名稱：MySQLCHAR和VARCHAR數(shù)據(jù)類型介紹
轉(zhuǎn)載源于：http://weahome.cn/article/gjdohj.html