MySQL中資料型別的驗證

CHAR

char (M) M字元，長度是M*字元編碼長度，M最大255。

驗證如下：

?

mysql> create table t1(name char(256)) default charset=utf8;ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT insteadmysql> create table t1(name char(255)) default charset=utf8;Query OK, 0 rows affected (0.06 sec)mysql> insert into t1 values(repeat('整',255));Query OK, 1 row affected (0.00 sec)mysql> select length(name),char_length(name) from t1;+--------------+-------------------+| length(name) | char_length(name) |+--------------+-------------------+| 765 | 255 |+--------------+-------------------+1 row in set (0.00 sec)

VARCHAR

VARCHAR(M)，M同樣是字元，長度是M*字元編碼長度。它的限制比較特別，行的總長度不能超過65535位元組。

?

mysql> create table t1(name 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 BLOBsmysql> create table t1(name varchar(65534));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 BLOBsmysql> create table t1(name varchar(65533));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 BLOBsmysql> create table t1(name varchar(65532));Query OK, 0 rows affected (0.08 sec)

注意，以上表的預設字符集是latin1，字元長度是1個位元組，所以對於varchar，最大隻能指定65532位元組的長度。

如果是指定utf8，則最多隻能指定21844的長度

?

mysql> create table t1(name varchar(65532)) default charset=utf8;ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT insteadmysql> create table t1(name varchar(21845)) default charset=utf8;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 BLOBsmysql> create table t1(name varchar(21844)) default charset=utf8;Query OK, 0 rows affected (0.07 sec)

注意：行的長度最大為65535，只是針對除blob，text以外的其它列。

?

1234

mysql> create table t1(name varchar(65528),hiredate datetime) default charset=latin1;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 BLOBsmysql> create table t1(name varchar(65527),hiredate datetime) default charset=latin1;Query OK, 0 rows affected (0.01 sec)

確實，datetime佔了5個位元組。

TEXT，BLOB

?

mysql> create table t1(name text(255));Query OK, 0 rows affected (0.01 sec)mysql> create table t2(name text(256));Query OK, 0 rows affected (0.01 sec)mysql> show create table t1G*************************** 1. row ***************************Table: t1Create Table: CREATE TABLE `t1` (`name` tinytext) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> show create table t2G*************************** 1. row ***************************Table: t2Create Table: CREATE TABLE `t2` (`name` text) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

透過上面的輸出可以看出text可以定義長度，如果範圍小於28（即256）則為tinytext，如果範圍小於216（即65536），則為text， 如果小於224，為mediumtext，小於232，為longtext。

上述範圍均是位元組數。

如果定義的是utf8字符集，對於text，實際上只能插入21845個字元

?

mysql> create table t1(name text) default charset=utf8;Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values(repeat('整',21846));ERROR 1406 (22001): Data too long for column 'name' at row 1mysql> insert into t1 values(repeat('整',21845));Query OK, 1 row affected (0.05 sec)

DECIMAl

關於Decimal，官方的說法有點繞，

?

1

Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes. The storage required for excess digits is given by the following table.

還提供了一張對應表

對於以上這段話的解讀，有以下幾點：

1. 每9位需要4個位元組，剩下的位數所需的空間如上所示。

2. 整數部分和小數部分是分開計算的。

譬如 Decimal(6,5)，從定義可以看出，整數佔1位，整數佔5位，所以一共佔用1+3=4個位元組。

如何驗證呢？可透過InnoDB Table Monitor

如何啟動InnoDB Table Monitor，可參考：http://dev.mysql.com/doc/refman/5.7/en/innodb-enabling-monitors.html

?

mysql> create table t2(id decimal(6,5));Query OK, 0 rows affected (0.01 sec)mysql> create table t3(id decimal(9,0));Query OK, 0 rows affected (0.01 sec)mysql> create table t4(id decimal(8,3));Query OK, 0 rows affected (0.01 sec)mysql> CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;Query OK, 0 rows affected, 1 warning (0.01 sec)

結果會輸出到錯誤日誌中。

檢視錯誤日誌：

對於decimal(6,5),整數佔1位，小數佔5位，一共佔用空間1+3=4個位元組

對於decimal(9,0),整數部分9位，每9位需要4個位元組，一共佔用空間4個位元組

對於decimal(8,3),整數佔5位，小數佔3位，一共佔用空間3+2=5個位元組。

至此，常用的MySQL資料型別驗證完畢~

對於CHAR，VARCHAR和TEXT等字元型別，M指定的都是字元的個數。對於CHAR，最大的字元數是255。對於VARCHAR，最大的字元數與字符集有關，如果字符集是latin1，則最大的字元數是65532（畢竟每一個字元只佔用一個位元組），對於utf8，最大的字元數是21844，因為一個字元佔用三個位元組。本質上，VARCHAR更多的是受到行大小的限制（最大為65535個位元組）。對於TEXT，不受行大小的限制，但受到自身定義的限制。