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

于以上就段话的解读,有以下几点:

  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)

结果会输出及不当日志中。

查阅错误日志:

图片 2

对此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,不深受行大小的范围,但遭受自身定义之限。

 

网站地图xml地图