1. CHAR 語法： CHAR (length) 其中length的取值範圍為1到255。 超過255則報錯： mysql> create table s3 ( value char(1023) primary key); ERROR 1074 (42000): Column length too ...
1. CHAR
語法：
CHAR (length)
其中length的取值範圍為1到255。
超過255則報錯：
mysql> create table s3 ( value char(1023) primary key);
ERROR 1074 (42000): Column length too big for column 'value' (max = 255); use BLOB or TEXT instead
例如：CHAR(20)表示最多包含20個字元的字元串。存儲時，不足20個字元時後面加空格。
在預設情況下，提取時，自動刪除掉後面的空格；在PAD_CHAR_TO_FULL_LENGTH時，則不會刪除後面的空格，即提取後的結果包含20個字元。
假定存在如下數據：
mysql> show create table s1;
+-------+-----------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------+
| s1 | CREATE TABLE `s1` (
`value` char(10) NOT NULL,
PRIMARY KEY (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into s1 values ('ABC') , ('1234567890') , (' 12 3 45 ');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
在預設情況下:
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> select length(value) , char_length(value) , value from s1;
+---------------+--------------------+------------+
| length(value) | char_length(value) | value |
+---------------+--------------------+------------+
| 8 | 8 | 12 3 45 |
| 10 | 10 | 1234567890 |
| 3 | 3 | ABC |
+---------------+--------------------+------------+
3 rows in set (0.00 sec)
在sql_mode為PAD_CHAR_TO_FULL_LENGTH時，將不會刪除CHAR類型的字元串後面附加的空格。
mysql> set sql_mode='pad_char_to_full_length';
Query OK, 0 rows affected (0.00 sec)
mysql> select length(value) , char_length(value) , value from s1;
+---------------+--------------------+------------+
| length(value) | char_length(value) | value |
+---------------+--------------------+------------+
| 10 | 10 | 12 3 45 |
| 10 | 10 | 1234567890 |
| 10 | 10 | ABC |
+---------------+--------------------+------------+
3 rows in set (0.00 sec)
mysql> select @sql_mode;
+-----------+
| @sql_mode |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
mysql> select @@sql_mode;
+-------------------------+
| @@sql_mode |
+-------------------------+
| PAD_CHAR_TO_FULL_LENGTH |
+-------------------------+
1 row in set (0.00 sec)
2. VARCHAR
語法：
VARCHAR(max-length)
其中，max-length是該類型字元串最多可以存儲的字元的個數。整個欄位占用的空間大小是長度占有的位元組數加上實際字元所占有的位元組數。當字元串長度在0到255時，長度占1個位元組，當字元串長度超過255個字元時，長度占2個位元組。VARCHAR類型最多65535個字元。
mysql> create table s13 ( value varchar(10) primary key);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into s13 values ('ABCD') , (' A B ');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select @@sql_mode;
+-------------------------+
| @@sql_mode |
+-------------------------+
| PAD_CHAR_TO_FULL_LENGTH |
+-------------------------+
1 row in set (0.00 sec)
mysql> select length(value), char_length(value), value from s13;
+---------------+--------------------+-------+
| length(value) | char_length(value) | value |
+---------------+--------------------+-------+
| 5 | 5 | A B |
| 4 | 4 | ABCD |
+---------------+--------------------+-------+
2 rows in set (0.00 sec)
從上述結果可以看到，PAD_CHAR_TO_FULL_LENGTH模式對於VARCHAR類型沒有作用，這個模式只對CHAR類型有作用。
3. 欄位長度限制
(1)主鍵為VARCHAR類型時的長度限製為3072個字元。
mysql> create table s5 ( value varchar(65535) primary key);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create table s5 ( value varchar(3072) primary key);
Query OK, 0 rows affected (0.02 sec)
(2)VARCHAR類型的欄位的長度大於或等於65536時，自動轉化為TEXT相關類型。
原因就是因為VARCHAR類型在存儲時要保存字元串長度，而且最多占用2個位元組。
mysql> create table s6 ( value varchar(3072) primary key , v varchar(65536));
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------+
| Note | 1246 | Converting column 'v' from VARCHAR to TEXT |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table s6;
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
| s6 | CREATE TABLE `s6` (
`value` varchar(3072) NOT NULL,
`v` mediumtext,
PRIMARY KEY (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(3)一個行中的VARCHAR和CHAR類型的字元串的最大長度總和不能超過65535個字元。
mysql> create table s12 ( value int primary key , v varchar(65535));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table s12 ( value int primary key , v varchar(65500));
Query OK, 0 rows affected (0.03 sec)
(4)當CHAR類型的欄位插入超過定義長度的數據時，將自動截取為定義長度。
mysql> show create table s1;
+-------+---------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------+
| s1 | CREATE TABLE `s1` (
`value` char(10) NOT NULL,
PRIMARY KEY (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into s1 values ('12345678901234');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'value' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from s1;
+--------------+
| value |
+--------------+
| |
| 1234567890 |
| 中 |
+--------------+
3 rows in set (0.00 sec)
(5)當VARCHAR類型的欄位在插入數據時長度超過定義的長度，將自動截取到定義的長度。
mysql> insert into s13 values ('12345678901234');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'value' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from s13;
+------------+
| value |
+------------+
| A B |
| 1234567890 |
| ABCD |
+------------+
3 rows in set (0.00 sec)
4. 字元集
預設情況下，數據表的字元集為latin1。可以在create table或者alter table時修改數據表的字元集為utf8。
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
mysql> show create table s1;
+-------+---------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------+
| s1 | CREATE TABLE `s1` (
`value` char(10) NOT NULL,
PRIMARY KEY (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select length(value) , char_length(value) , value from s1;
+---------------+--------------------+-------+
| length(value) | char_length(value) | value |
+---------------+--------------------+-------+
| 3 | 1 | 中 |
+---------------+--------------------+-------+
1 row in set (0.00 sec)
LENGTH表示位元組數，CHAR_LENGTH表示字元數。在使用UTF8字元集時，一個漢字的位元組數為3，即LENGTH為3,；而一個漢字的字元數仍然為1，即CHAR_LENGTH仍然為1。
5. BINARY
BINARY用法與CHAR類似，只是可以存儲所有字元值，包括空字元(ASCII值為0)，而且末尾補足長度的字元為空字元，而不是空格。BINARY類型最大長度255個位元組，並非255個字元。
mysql> create table b1 (value binary(10) primary key);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into b1 values('1234567890') , ('123');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select value,hex(value) from b1;
+------------+----------------------+
| value | hex(value) |
+------------+----------------------+
| 123 | 31323300000000000000 |
| 1234567890 | 31323334353637383930 |
+------------+----------------------+
2 rows in set (0.00 sec)
使用unhex()函數可以將十六進位字元串轉化為不可見字元組成的字元串，插入不可見字元的例子如下：
mysql> insert into b1 values ( unhex('01020300010203'));
Query OK, 1 row affected (0.01 sec)
mysql> select value,hex(value) from b1;
+------------+----------------------+
| value | hex(value) |
+------------+----------------------+
| | 01020300010203000000 |
+------------+----------------------+
1 row in set (0.00 sec)
當BINARY類型超過255個位元組時，將報錯。
mysql> create table b10 ( value int primary key, v binary(256));
ERROR 1074 (42000): Column length too big for column 'v' (max = 255); use BLOB or TEXT instead
6. VARBINARY
VARBINARY與BINARY類似，二者之間差異與 VARCHAR和CHAR之間的差異類似。而VARBINARY和VARCHAR也很類似，二者之間的差異與BINARY和CHAR之間的差異類似。
當最大長度為0到255位元組時，長度首碼為1個位元組，欄位總長度為1+最大長度個位元組。
當最大長度為256到65535位元組時，長度首碼為2個位元組。
mysql> create table b2 ( value varbinary(10) primary key);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into b2 values ( '1234567890'), ("123"), (unhex('01020300010203'));
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select value, hex(value) from b2;
+------------+----------------------+
| value | hex(value) |
+------------+----------------------+
| | 01020300010203 |
| 123 | 313233 |
| 1234567890 | 31323334353637383930 |
+------------+----------------------+
3 rows in set (0.00 sec)
當VARBINARY類型超過65535位元組時，將自動轉換為BLOB相關類型。
mysql> create table b9 ( value int primary key, v varbinary(65536));
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show warnings;
+-------+------+----------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------+
| Note | 1246 | Converting column 'v' from VARBINARY to BLOB |
+-------+------+----------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table b9;
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| b9 | CREATE TABLE `b9` (
`value` int(11) NOT NULL,
`v` mediumblob,
PRIMARY KEY (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
7. TEXT
有四種TEXT相關類型,它們之間的差別在於允許的最大長度不相同。
TINYTEXT：1到255個位元組。欄位長度首碼1個位元組。
TEXT：256到65535個位元組。欄位長度首碼2個位元組。
MEDIUMTEXT：65536到2的24次方-1個位元組。欄位長度首碼3個位元組。
LONGTEXT：2的24次方到2的32次方-1個位元組。欄位長度首碼4個位元組。
在創建數據表時，在CREATE TABLE語句中只使用TEXT類型即可，MySQL將根據TEXT(N)中的N自動決定實際使用TINYTEXT或TEXT或MEDIUMTEXT或LONGTEXT。在使用其它三個類型時則沒有這種根據長度自動決定實際類型的功能，而且不能指定最大長度。
mysql> create table b3 ( value int primary key , v text(30) );
Query OK, 0 rows affected (0.04 sec)
mysql> show create table b3;
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
| b3 | CREATE TABLE `b3` (
`value` int(11) NOT NULL,
`v` tinytext,
PRIMARY KEY (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table b4 ( value int primary key , v text(65536) );
Query OK, 0 rows affected (0.02 sec)
mysql> show create table b4;
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| b4 | CREATE TABLE `b4` (
`value` int(11) NOT NULL,
`v` mediumtext,
PRIMARY KEY (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table b5 ( value int primary key , v text(300) );
Query OK, 0 rows affected (0.02 sec)
mysql> show create table b5;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| b5 | CREATE TABLE `b5` (
`value` int(11) NOT NULL,
`v` text,
PRIMARY KEY (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
TEXT類型和VARCHAR類型類似，並不會進行長度補足的操作。
8. BLOB
BLOB相關類型也有類似的四種類型：
有四種BLOB相關類型,它們之間的差別在於允許的最大長度不相同。
TINYBLOB：1到255個位元組。欄位長度首碼1個位元組。
BLOB：256到65535個位元組。欄位長度首碼2個位元組。
MEDIUMBLOB：65536到2的24次方-1個位元組。欄位長度首碼3個位元組。
LONGBLOB：2的24次方到2的32次方-1個位元組。欄位長度首碼4個位元組。
mysql> create table b11 ( value int primary key, v blob(100));
Query OK, 0 rows affected (0.03 sec)
在定義數據表時，如果使用BLOB(N)的形式定義欄位，則實際欄位類型被自動調整為最適合最大長度N的BLOB相關類型。
mysql> show create table b11;
+-------+---------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------+
| b11 | CREATE TABLE `b11` (
`value` int(11) NOT NULL,
`v` tinyblob,
PRIMARY KEY (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
BLOB相關類型在實際存儲和讀取時，不會發生長度補足操作和截取操作，而是存儲和讀取原始二進位數據內容。
mysql> insert into b11 values (1,unhex('01020300010203')),(2,'1234567890');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select value, v,hex(v) from b11;
+-------+------------+----------------------+
| value | v | hex(v) |
+-------+------------+----------------------+
| 1 | | 01020300010203 |
| 2 | 1234567890 | 31323334353637383930 |
+-------+------------+----------------------+
2 rows in set (0.00 sec)
