程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL中數據類型的驗證

MySQL中數據類型的驗證

編輯：MySQL綜合教程

MySQL中數據類型的驗證。本站提示廣大學習愛好者：（MySQL中數據類型的驗證）文章只能為提供參考，不一定能成為您想要的結果。以下是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 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異樣是字符，長度是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 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)

留意，以上表的默許字符集是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 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)

留意：行的長度最年夜為65535，只是針對除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)

確切，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 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可以界說長度，假如規模小於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 1
mysql> insert into t1 values(repeat('整',21845));
Query OK, 1 row affected (0.05 sec)

DECIMAl

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

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，不受行年夜小的限制，但遭到本身界說的限制。