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)
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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2121119/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL資料型別操作(char與varchar)MySql資料型別
- MySQL字元資料型別char與varchar的區別MySql字元資料型別
- MySQL中CHAR和VARCHAR區別MySql
- Oracle的資料型別：char/varchar2Oracle資料型別
- NTMySQL中varchar和char型別的區別heeMySql型別
- MySQL 中 VARCHAR 最大長度及 CHAR 和 VARCHAR 的區別MySql
- PLSQL Language Reference-PL/SQL資料型別-SQL資料型別-CHAR和VARCHAR2變數SQL資料型別變數
- 高效能MySQL第四章 Schema與資料型別優化 VARCHAR和CHARMySql資料型別優化
- MySQL中資料型別（char(n)、varchar(n)、nchar(n)、nvarchar(n)的區別）MySql資料型別
- 談MySQL中char varchar區別MySql
- Oracle 資料型別CHAR, NCHAR, VARCHAR2, NVARCHAR2Oracle資料型別
- varchar和char的區別
- char ,varchar2和varchar的區別
- Oracle CHAR，VARCHAR，VARCHAR2,nvarchar型別的區別與使用Oracle型別
- char和varchar2的區別
- 【基礎】Oracle CHAR，VARCHAR，VARCHAR2,nvarchar型別的區別與使用Oracle型別
- javascript資料型別和作用域介紹JavaScript資料型別
- 【Redis】資料型別介紹Redis資料型別
- Rust 資料型別介紹Rust資料型別
- Oracle資料型別介紹Oracle資料型別
- C# - char型別的一些介紹C#型別
- char(n)和varchar2(n)區別
- MySQL全面瓦解3：資料型別介紹與分析MySql資料型別
- 關於mysql設定varchar 欄位的預設值''和null的區別，以及varchar和char的區別MySqlNull
- char,nchar,varchar,nvarchar的區別和特點
- mysql的varchar(N)和int(N)的含義及其與char區別MySql
- mysql字元型別varchar（）比較MySql字元型別
- Tuxedo資料buffer基本型別介紹UX型別
- Mysql中varchar與char的區別以及varchar(30)中的30代表的涵義MySql
- 例項操作mysql varchar型別求和MySql型別
- ABAP中Char型別資料轉換成Decimal型別型別Decimal
- 【區別】Oracle官方文件中有關NVARCHAR2、VARCHAR2和VARCHAR資料型別的描述Oracle資料型別
- MySQL中int、char、varchar的效能淺談MySql
- mysql儲存資料，varchar型別中的資料變成了科學計數法？MySql型別
- GBase8s資料型別介紹資料型別
- javascript資料型別轉換簡單介紹JavaScript資料型別
- javascript數字和字串資料型別轉換簡單介紹JavaScript字串資料型別
- MySQL5.7 JSON型別使用介紹MySqlJSON型別