MySQL中數(shù)據(jù)類型的驗(yàn)證

更新時(shí)間：2016年04月13日 11:10:33 作者：iVictor

這篇文章主要介紹了MySQL中數(shù)據(jù)類型的驗(yàn)證的相關(guān)資料,需要的朋友可以參考下

CHAR

char (M) M字符，長(zhǎng)度是M*字符編碼長(zhǎng)度，M最大255。

驗(yàn)證如下：

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 instead
mysql> 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同樣是字符，長(zhǎng)度是M*字符編碼長(zhǎng)度。它的限制比較特別，行的總長(zhǎng)度不能超過65535字節(jié)。

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

注意，以上表的默認(rèn)字符集是latin1，字符長(zhǎng)度是1個(gè)字節(jié)，所以對(duì)于varchar，最大只能指定65532字節(jié)的長(zhǎng)度。

如果是指定utf8，則最多只能指定21844的長(zhǎng)度

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 instead
mysql> 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 BLOBs
mysql> create table t1(name varchar(21844)) default charset=utf8;
Query OK, 0 rows affected (0.07 sec)

注意：行的長(zhǎng)度最大為65535，只是針對(duì)除blob，text以外的其它列。

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

確實(shí)，datetime占了5個(gè)字節(jié)。

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 t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`name` tinytext
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`name` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

通過上面的輸出可以看出text可以定義長(zhǎng)度，如果范圍小于28（即256）則為tinytext，如果范圍小于216（即65536），則為text，如果小于224，為mediumtext，小于232，為longtext。

上述范圍均是字節(jié)數(shù)。

如果定義的是utf8字符集，對(duì)于text，實(shí)際上只能插入21845個(gè)字符

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 1
mysql> insert into t1 values(repeat('整',21845));
Query OK, 1 row affected (0.05 sec)

DECIMAl

關(guān)于Decimal，官方的說法有點(diǎn)繞，

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 “l(fā)eftover” digits require some fraction of four bytes. The storage required for excess digits is given by the following table.

還提供了一張對(duì)應(yīng)表

對(duì)于以上這段話的解讀，有以下幾點(diǎn)：

1. 每9位需要4個(gè)字節(jié)，剩下的位數(shù)所需的空間如上所示。

2. 整數(shù)部分和小數(shù)部分是分開計(jì)算的。

譬如 Decimal(6,5)，從定義可以看出，整數(shù)占1位，整數(shù)占5位，所以一共占用1+3=4個(gè)字節(jié)。

如何驗(yàn)證呢？可通過InnoDB Table Monitor

如何啟動(dòng)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)

結(jié)果會(huì)輸出到錯(cuò)誤日志中。

查看錯(cuò)誤日志：

對(duì)于decimal(6,5),整數(shù)占1位，小數(shù)占5位，一共占用空間1+3=4個(gè)字節(jié)

對(duì)于decimal(9,0),整數(shù)部分9位，每9位需要4個(gè)字節(jié)，一共占用空間4個(gè)字節(jié)

對(duì)于decimal(8,3),整數(shù)占5位，小數(shù)占3位，一共占用空間3+2=5個(gè)字節(jié)。

至此，常用的MySQL數(shù)據(jù)類型驗(yàn)證完畢~

對(duì)于CHAR，VARCHAR和TEXT等字符類型，M指定的都是字符的個(gè)數(shù)。對(duì)于CHAR，最大的字符數(shù)是255。對(duì)于VARCHAR，最大的字符數(shù)與字符集有關(guān)，如果字符集是latin1，則最大的字符數(shù)是65532（畢竟每一個(gè)字符只占用一個(gè)字節(jié)），對(duì)于utf8，最大的字符數(shù)是21844，因?yàn)橐粋€(gè)字符占用三個(gè)字節(jié)。本質(zhì)上，VARCHAR更多的是受到行大小的限制（最大為65535個(gè)字節(jié)）。對(duì)于TEXT，不受行大小的限制，但受到自身定義的限制。

您可能感興趣的文章: