mysql 01类别_01-mysql中的数据类型

mysql中的列数据类型：

数值型、字符串类型、日期/时间类型3种

几种列类型描述使用了下述惯例：

· M#表示最大显示宽度。最大有效显示宽度是255。

· D#适用于浮点和定点类型，表示小数点后面的位数。最大可能的值是30，但不应大于M-2。

· [] #方括号(‘[’和‘]’)表示可选部分。

1、 数值类型

严格数值类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)

近似数值数据类型(FLOAT、REAL 和DOUBLE PRECISION), 并在此基础上做了扩展

扩展后增加了TINYINT、MEDIUMINT和BIGINT这3种长度不同的整型，并增加了BIT类型，用来存放位数据

几个注意点：

M指示最大显示宽度。最大有效显示宽度是255

如果为一个数值列指定ZEROFILL，MySQL自动为该列添加UNSIGNED属性！！！！！！！！！！！

SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的一个别名。

在整数列定义中：SERIAL DEFAULT VALUE是NOT NULL AUTO_INCREMENT UNIQUE的一个别名。

警告：应当清楚，当使用在整数值(其中一个是UNSIGNED类型)之间使用减号时，结果是无符号

整数类型：

整数类型 字节 取值范围

TINYINT[(M)] [UNSIGNED] [ZEROFILL]1个字节 -128~127(有符号) 0~255(无符号)

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]2个字节 -32768~32767(有符号) 0~65535(无符号)

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]3个字节 -8388608~8388607(有符号) 0~16777215(无符号)

INT[(M)] [UNSIGNED] [ZEROFILL]4个字节 -2147483648~2147483647(有符号) 0~4294967295(无符号) 若不指定宽度M，则默认为int(11),一般配合zerofill,即使用"0" 填充

BIGINT[(M)] [UNSIGNED] [ZEROFILL]8个字节 -9223372036854775808~9223372036854775807(有符号) 0~18446744073709551615(无符号)

如果超出类型范围的操作，会发生"Out of range" 错误提示。为了避免此类问题发生，在选择数据类型时要根据应用的实际情况确定其取值范围

注意：

如果指定zerofill属性，会自动添加unsigned属性，虽然设置了zerofill属性，但使用select查询时填充的0并不会显示出来

如果没有unsigned属性，默认是有符号的，而INT类型，如果如果无unsigned属性，有符号默认长度为int(11), 有unsigned属性，无符号默认长度(10)

如果插入的数字的位数大于M指定的位数，不会对插入的数据有任何影响，还是按照类型的实际精度进行保存，即数值会正确被插入，但是指定的数值最大不能超出该类型的取值范围

整数类型还有一个属性：AUTO_INCREMENT。在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型(一般从1 开始，每行增加1)

一个表中最多只能有一个AUTO_INCREMENT列，对于任何想要使用AUTO_INCREMENT的列，应该定义为NOT NULL, 并定义为PRIMARY KEY或定义为UNIQUE键

使用方式如下：

CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY);

CREATE TABLE AI (ID INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(ID));

CREATE TABLE AI (ID INT AUTO_INCRE~ENT NOT NULL, UNIQUE(ID));

浮点类型：

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 4个字节 ±1.175494351E-38(有符号) ±3.402823466E+38

M是总位数，D是小数点后的位数。如果M和D被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位

FLOAT(p) [UNSIGNED] [ZEROFILL] p表示精度(以位数表示)，但MySQL只使用该值来确定是否结果列的数据类型为FLOAT或DOUBLE。

如果p为从0到24，数据类型变为没有M或D值的FLOAT。

如果p为从25到53，数据类型变为没有M或D值的DOUBLE。结果列单精度FLOAT数据类型相同。

FLOAT(p)语法与ODBC兼容。

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 8个字节 ±2.2250738585072014E-308 ±1.7976931348623157E+308

M是总位数，D是小数点后的位数。如果M和D被省略，根据硬件允许的限制来保存值。双精度浮点数精确到大约15位小数位！！！！

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]

REAL[(M,D)] [UNSIGNED] [ZEROFILL] 为DOUBLE的同义词。除了：如果SQL服务器模式包括REAL_AS_FLOAT选项，REAL是FLOAT的同义词而不是DOUBLE的同义词。

float和double在不指定精度时，默认会按照实际的精度(由实际的硬件和操作系统决定)来显示

定点数类型

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] M+2个字节 最大取值范围和DOUBLE相同，给定DECIMAL的有效值范围由M和D确定

M是位数的总数，D是小数点(标度)后面的位数。小数点和(负数)的‘-’符号不包括在M中。

如果D是0，则值没有小数点或分数部分。DECIMAL整数最大位数(M)为65。支持的十进制数的最大位数(D)是30。

如果D被省略， 默认是0。如果M被省略， 默认是10。

所有DECIMAL列的基本计算(+，-，*，/)用65位精度完成。

DEC[(M[,D])] [UNSIGNED] [ZEROFILL],

NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL],

FIXED[(M[,D])] [UNSIGNED] [ZEROFILL] DECIMAL的同义词，FIXED同义词适用于与其它服务器的兼容性。

定点数在MySQL 内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

浮点数和定点数都可以用类型名称后加(M, D)的方式来进行表示，"(M, D)表示该值一共显示M位数字(整数位＋小数位)，其中D位位于小数点后面，M和D又称为精度和标度。

例如，定义为float(7,4)的一个列可以显示为-999.9999

MySQL保存值时进行四舍五入，因此如果在float(7,4)列内插入999.00009, 近似结果是999.0001

decimal在不指定精度时，默认的整数位为10, 默认的小数位为0

示例：

create table t1 (

id1 float(5,2) default null,

id2 double(5,2) default null,

id3 decimal(5,2) default null

);

mysql root@localhost:test1> desc t1

+-------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| id1 | float(5,2) | YES | | | |

| id2 | double(5,2) | YES | | | |

| id3 | decimal(5,2) | YES | | | |

+-------+--------------+------+-----+---------+-------+

mysql root@localhost:test1> insert into t1 values(1.23,1.23,1.23);

mysql root@localhost:test1> select * from t1

+------+------+------+

| id1 | id2 | id3 |

+------+------+------+

| 1.23 | 1.23 | 1.23 |

+------+------+------+

再向id1 和id2 字段中插入数据1.234 , 而id3字段中仍然插入1.23

mysql root@localhost:test1> insert into t1 values(1.234,1.234,1.23);

mysql root@localhost:test1> select * from t1

+------+------+------+

| id1 | id2 | id3 |

+------+------+------+

| 1.23 | 1.23 | 1.23 |

| 1.23 | 1.23 | 1.23 | #发现，id1、id2、id3都插入了表t1，但是id1和id2由于标度的限制，舍去了最后一位，数据变为了1.23

+------+------+------+

同时向id1、id2、id3字段中都插入数据1.234

mysql root@localhost:test1> insert into t1 values(1.234,1.234,1.234);

mysql root@localhost:test1> select * from t1

+------+------+------+

| id1 | id2 | id3 |

+------+------+------+

| 1.23 | 1.23 | 1.23 |

| 1.23 | 1.23 | 1.23 |

| 1.23 | 1.23 | 1.23 | #系统出现了一个Warning , 报告id3被截断

+------+------+------+

将id1、id2、id3字段的精度和标度全部去掉，再次插入数据1.23:

mysql root@localhost:test1> alter table t1 modify id1 float

mysql root@localhost:test1> alter table t1 modify id2 double

mysql root@localhost:test1> alter table t1 modify id3 decimal

mysql root@localhost:test1> desc t1

+-------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------+------+-----+---------+-------+

| id1 | float | YES | | | |

| id2 | double | YES | | | |

| id3 | decimal(10,0) | YES | | | |

+-------+---------------+------+-----+---------+-------+

mysql root@localhost:test1> insert into t1 values(1.234,1.234,1.234);

mysql root@localhost:test1> select * from t1

+-------+-------+-----+

| id1 | id2 | id3 |

+-------+-------+-----+

| 1.23 | 1.23 | 1 |

| 1.23 | 1.23 | 1 |

| 1.23 | 1.23 | 1 |

| 1.234 | 1.234 | 1 | #可以发现id1、id2字段中可以正常插入数据，而id3字段的小数位被截断

+-------+-------+-----+

可知：

float和double如果不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；

decimal如果不写精度和标度，则按照默认值decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错

位类型:

BIT[(M)] 1~8个字节 最大值BIT(64)

M表示每个值的位数，范围为从1到64。如果M省略不写，默认为1。

对于位字段，直接使用SELECT 命令将不会看到结果，可以用bin()(显示为二进制格式)或者hex()(显示为十六进制格式)函数进行读取

指定值，可使用b'value'符。value是一个用0和1编写的二进制值。如，b'111'和b'100000000'分别表示7和128。

如果为BIT(M)列分配的值的长度小于M位，在值的左边用0填充。例如，为BIT(6)列分配一个值b'101'，其效果与分配b'000101'相同。

mysql root@localhost:test1> create table t2 (id bit)

mysql root@localhost:test1> desc t2

+-------+--------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------+------+-----+---------+-------+

| id | bit(1) | YES | | | |

+-------+--------+------+-----+---------+-------+

mysql root@localhost:test1> insert into t2 values(1)

mysql root@localhost:test1> select * from t2

+----+

| id |

+----+

| |

+----+

mysql root@localhost:test1> select bin(id),hex(id) from t2

+---------+---------+

| bin(id) | hex(id) |

+---------+---------+

| 1 | 1 |

+---------+---------+

数据插入bit类型字段时，首先转换为二进制，如果位数允许，将成功插入；如果插入的数转换为二进制后长度大于定义的位数，则会报错

mysql root@localhost:test1> insert into t2 values(2) #2的二进制码为10，位长度是2大于定义的长度1，报错

(1406, u"Data too long for column 'id' at row 1")

将ID定义修改为bit(2)后，重新插入，插入成功：

mysql root@localhost:test1> alter table t2 modify id bit(2);

mysql root@localhost:test1> insert into t2 values(2);

mysql root@localhost:test1> select bin(id),hex(id) from t2;

+---------+---------+

| bin(id) | hex(id) |

+---------+---------+

| 1 | 1 |

| 10 | 2 |

+---------+---------+

2、日期和时间类型

如果要用来表示年月日，通常用DATE来表示

如果只用来表示时分秒，通常用TIME来表示

如果要用来表示年月日时分秒，通常用DATETIME表示

日期和时间类型 字节 最小值 最大值

DATE 4 1000-01-01 9999-12-31 允许使用字符串或数字为DATE列分配值

TIME 3 -838:59:59 838:59:59 允许使用字符串或数字为TIME列分配值

DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59 允许使用字符串或数字为DATETIME列分配值

YEAR 1 1901 2155 默认是4位格式(1901~2155和0000),2位(70~69表示1970到2069年；00~69表示2000~2069年)

TIMESTAMP 4 19700101080001 2038年的某个时刻 用于INSERT或UPDATE操作时记录日期和时间。

不分配一个值，表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间！！！

也可分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间！！！！！

TIMESTAMP值返回后显示为'YYYY-MM-DD HH:MM:SS'格式的字符串，显示宽度固定为19个字符。

如果想要获得数字值，应在TIMESTAMP列添加+0！！！！！

每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。

MySQL 中日期和时间类型的零值表示:

数据类型 零值表示

DATETIME 0000-00-00 00:00:00

DATE 0000-00-00

TIMESTAMP 00000000000000

TIME 00:00:00

YEAR 0000

如果试图插入一个不合法的日期，MySQL将给出警告或错误。

可以使用ALLOW_INVALID_DATES SQL模式让MySQL接受某些日期，例如'1999-11-31'。

mysql root@localhost:test1> create table t(d date,t time,dt datetime)

mysql root@localhost:test1> desc t

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| d | date | YES | | | |

| t | time | YES | | | |

| dt | datetime | YES | | | |

+-------+----------+------+-----+---------+-------+

mysql root@localhost:test1> insert into t values(now(),now(),now())

mysql root@localhost:test1> select * from t

+------------+----------+---------------------+

| d | t | dt |

+------------+----------+---------------------+

| 2019-02-26 | 15:29:31 | 2019-02-26 15:29:31 |

+------------+----------+---------------------+

mysql root@localhost:test1> create table t(id1 timestamp)

mysql root@localhost:test1> desc t

+-------+-----------+------+-----+-------------------+-----------------------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-----------+------+-----+-------------------+-----------------------------+

| id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | 系统自动创建了默认值CURRENT_TIMESTAMP(系统日期)

+-------+-----------+------+-----+-------------------+-----------------------------+

插入一个NILL值：

mysql root@localhost:test1> insert into t values(null)

mysql root@localhost:test1> select * from t

+---------------------+

| id1 |

+---------------------+

| 2019-02-26 15:32:40 | 正确插入了系统日期

+---------------------+

注意:

MySQL只给表中的第一个TIMESTAMP字段设置默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值设置为0值

mysql root@localhost:test1> alter table t add id2 timestamp

mysql root@localhost:test1> desc t

+-------+-----------+------+-----+---------------------+-----------------------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-----------+------+-----+---------------------+-----------------------------+

| id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

| id2 | timestamp | NO | | 0000-00-00 00:00:00 | |

+-------+-----------+------+-----+---------------------+-----------------------------+

mysql root@localhost:test1> show create table t

+-------+-----------------------------------------------------------------------------------+

| Table | Create Table |

+-------+-----------------------------------------------------------------------------------+

| t | CREATE TABLE `t` ( |

| | `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |

| | `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' |

| | ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+-----------------------------------------------------------------------------------+

可以修改id2的默认值为其他常量日期，但是不能再修改为current_timestamp

因为MySQL 规定TIMESTAMP 类型字段只能有一列的默认值为current_timestamp !!!!!!!!!

如果强制修改，系统会报错

TIMESTAMP还有一个重要特点，就是和时区相关。

当插入日期时，会先转换为本地时区后存放；

而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。这样两个不同时区的用户看到的同一个日期可能是不一样的

默认值为current_timestamp的TIMESTAMP列，插入NULL后其值默认为插入那一时刻的时间，保持不变，但如果更改时区，其值会发生改变，改变为更改的时区插入时的时间

表中的笫一个TIMESTAMP列自动设置为系统时间。如果在一个TIMESTAMP列中插入NULL, 则该列值将自动设置为当前的日期和时间。

在插入或更新一行但不明确给TIMESTAMP列赋值时也会自动设置该列的值为当前的日期和时间，当插入的值超出取值范围时， MySQL认为该值溢出，使用"0000-00-00 00:00:00"进行填补。

mysql root@localhost:test1> select * from t

+---------------------+---------------------+

| id1 | id2 |

+---------------------+---------------------+

| 2019-02-26 15:32:40 | 0000-00-00 00:00:00 |

+---------------------+---------------------+

mysql root@localhost:test1> set time_zone="+9:00"

mysql root@localhost:test1> show variables like 'time_zone';

+---------------+--------+

| Variable_name | Value |

+---------------+--------+

| time_zone | +09:00 |

+---------------+--------+

mysql root@localhost:test1> select * from t

+---------------------+---------------------+

| id1 | id2 |

+---------------------+---------------------+

| 2019-02-26 16:32:40 | 0000-00-00 00:00:00 | #更改时区后，其值改变了

+---------------------+---------------------+

YEAR 类型主要用来表示年份，当应用只需要记录年份时，用YEAR 比DATE 将更节省空间

mysql root@localhost:test1> create table t(y year)

mysql root@localhost:test1> desc t

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| y | year(4) | YES | | | |

+-------+---------+------+-----+---------+-------+

mysql root@localhost:test1> insert into t values(2100)

mysql root@localhost:test1> select * from t

+------+

| y |

+------+

| 2100 |

+------+

YYYY-MM-DD HH:MM:SS 或YY-MM-DD HH:MM:SS格式的字符串，允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。

例如:

“98-12-31 11:30:45”、“98.12.31 11+30+45”、“98/12/31 11*30*45”和“98@12@31 11^30^45”是等价的

对于包括日期部分间割符的字符串值，如果日和月的值小于10，不需要指定两位数： “1979-6-9”与“1979-06-09” 相同

对于包括时间部分间割符的字符串值，如果时、分和秒的值小于10，不需要指定两位数：“1979-10-30 1:2:3”与“1979-10-30 01:02:03” 相同

YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的没有间隔符的字符串，假定字符串对于日期类型是有意义的。

例如:

“19970523091528”和“970523091528”被解释为“1997-05-23 09:15:28”，但“971122129015”是不合法的(1997-11-22 12:90:15是一个没有意义的分钟部分)，将变为“0000-00-00 00:00:00”

数字值应为6、8、12或者14位长：

如果数值是8或14位长，则假定为YYYYMMDD或YYYYMMDDHHMMSS格式，前4位数表示年。

如果数值是6或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式， 前2位数表示年。其它数字被解释为仿佛用零填充到了最近的长度。

不应使用少于6字符的字符串。例如，如果你指定'9903'，认为它表示1999年3月，MySQL将在你的表内插入一个“零”日期值。

在非严格模式，MySQL服务器只对日期的合法性进行基本检查：年、月和日的范围分别是1000到9999、00到12和00到31。

任何包含超出这些范围的部分的日期被转换成'0000-00-00'！！！！！

请注意仍然允许你保存非法日期，例如'2002-04-31'。要想确保不使用严格模式时日期有效，应检查应用程序。

在严格模式，非法日期不被接受，并且不转换。

函数返回的结果，其值适合DATETIME、DATE或者TIMESTAMP上下文，例如NOW() (显示日期和时间)或CURRENT_DATE(显示日期)

无效DATETIME、DATE或者TIMESTAMP值被转换为相应类型的“零”值('0000-00-00 00:00:00'、'0000-00-00'或者00000000000000)！！！！！！！

3、字符串类型

字符串类型 字节 描述及存储需求

CHAR(M) M M为0~25 5之间的整数

VARCHAR(M) M为0~65535 之间的整数， 值的长度+1个字节

TINYBLOB 允许长度0~255字节，值的长度+1 个字节

BLOB 允许长度0~65535字节， 值的长度+2个字节

MEDIUMBLOB 允许长度0~167772150字节，值的长度+3个字节

LONGBLOB 允许长度0~4294967295字节，值的长度+4个字节

TINYTEXT 允许长度0~255字节，值的长度+2个字节

TEXT 允许长度0~65535字节，值的长度+2个字节

MEDIUMTEXT 允许长度O~167772150字节，值的长度+3个字节

LONGTEXT 允许长度0~4294967295字节，值的长度+4个字节

VARBINARY(M) 允许长度O~M个字节的变长字节字符串，值的长度+1个字节

BINARY(M) M 允许长度O~M个字节的定长字节字符串

3.1 char和varchar类型

CHAR和VARCHAR很类似，都用来保存MySQL中较短的字符串

二者的主要区别在于存储方式的不同：

CHAR列的长度固定为创建表时声明的长度，长度可以为从0～255的任何值；

VARCHAR列中的值为可变长字符串(赋值时，赋给几个字符串实际占几个长度，但是总长度不超过定义时的长度，不同于数值类型)，长度可以指定为0～255(5.0.3以前)或者65535(5.0.3以后)之间的值。

在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格。

赋值时，不管是char还是varchar，如果赋值时的长度超过定义时指定的长度，后面多余的长度就会被截断

mysql root@localhost:test1> create table vc(v varchar(4),c char(4))

mysql root@localhost:test1> desc vc

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| v | varchar(4) | YES | | | |

| c | char(4) | YES | | | |

+-------+------------+------+-----+---------+-------+

mysql root@localhost:test1> insert into vc values('ab ','ab ') #ab后均有4个空格

mysql root@localhost:test1> select * from vc

+------+----+

| v | c |

+------+----+

| ab | ab | #v列后面有4个空格，而c列后没有

+------+----+

mysql root@localhost:test1> select length(v),length(c) from vc

+-----------+-----------+

| length(v) | length(c) |

+-----------+-----------+

| 4 | 2 |

+-----------+-----------+

给两个字段分别追加一个"+"

mysql root@localhost:test1> select concat(v,'+'),concat(c,'+') from vc

+---------------+---------------+

| concat(v,'+') | concat(c,'+') |

+---------------+---------------+

| ab + | ab+ |

+---------------+---------------+

3.2、binary和varbinary类型

BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制的明文字符串

mysql root@localhost:test1> create table t(c binary(3))

mysql root@localhost:test1> desc t

+-------+-----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-----------+------+-----+---------+-------+

| c | binary(3) | YES | | | |

+-------+-----------+------+-----+---------+-------+

mysql root@localhost:test1> insert into t set c='a'

mysql root@localhost:test1> select *,hex(c),c='a',c='a\0',c='a\0\0' from t;

+-----+--------+-------+---------+-----------+

| c | hex(c) | c='a' | c='a\0' | c='a\0\0' | #后3列是逻辑判断

+-----+--------+-------+---------+-----------+

| a^@^@ | 610000 | 0 | 0 | 1 | #可见当保存BINARY值时，在值的最后通过填充"0x00"(零字节)以达到指定的字段定义长度

+-----+--------+-------+---------+-----------+

3.3、ENUM类型(枚举类型)

它的值范围需要在创建表时通过枚举方式显式指定:

对1~255个成员的枚举需要1个字节存储

对于255~65535个成员，需要2个字节存储，最多允许有65535个成员

mysql root@localhost:test1> create table t(gender enum('M','F'))

mysql root@localhost:test1> desc t

+--------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------+------+-----+---------+-------+

| gender | enum('M','F') | YES | | | |

+--------+---------------+------+-----+---------+-------+

mysql root@localhost:test1> insert into t values ('M'),('1'),('f'),(NULL)

mysql root@localhost:test1> select * from t

+--------+

| gender |

+--------+

| M |

| M | #插入1对应枚举中的第一个类型，插入2对应枚举类型中的第二个类型，插入3，超出范围，插入不成功

| F | #ENUM类型忽略大小写

| |

+--------+

ENUM类型只允许从值集合中选取单个值，而不能一次取多个值！！！！！！

3.4、set类型

Set和ENUM类型非常类似，也是一个字符串对象，里面可以包含0～64个成员(最大8个字节)，根据成员的不同，存储上也有所不同。每个二进制位一个成员

1～8 成员的集合，占1个字节

9～16 成员的集合，占2个字节

17～24 成员的集合，占3个字节

25～32 成员的集合，占4个字节

33～64 成员的集合，占8个字节

SET和ENUM除了存储之外，最主要的区别在于SET类型一次可以选取多个成员，而ENUM则只能选一个

mysql root@localhost:test1> create table t(col set('a','b','c','d'))

mysql root@localhost:test1> desc t

+-------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------------------+------+-----+---------+-------+

| col | set('a','b','c','d') | YES | | | |

+-------+----------------------+------+-----+---------+-------+

mysql root@localhost:test1> insert into t values('a,b'), ('a,d,a'),('a,b'),('a,c'),('a');

mysql root@localhost:test1> select * from t

+-----+

| col |

+-----+

| a,b |

| a,d | #包含重复成员的集合将只取一次

| a,b |

| a,c |

| a |

+-----+

5)TINYBLOB

最大长度为255(2**8–1)字节的BLOB列。

6)MEDIUMBLOB

最大长度为16,777,215(2**24–1)字节的BLOB列。

7)BLOB[(M)]

最大长度为65,535(2**16–1)字节的BLOB列。

可以给出该类型的可选长度M。如果给出，则MySQL将列创建为最小的但足以容纳M字节长的值的BLOB类型。

8)LONGBLOB

最大长度为4,294,967,295或4GB(2**32–1)字节的BLOB列。LONGBLOB列的最大有效(允许的)长度取决于客户端/服务器协议中配置最大包大小和可用的内存。

9)TINYTEXT

最大长度为255(2**8–1)字符的TEXT列。

10)MEDIUMTEXT

最大长度为16,777,215(2**24–1)字符的TEXT列。

11)TEXT[(M)]

最大长度为65,535(2**16–1)字符的TEXT列。

可以给出可选长度M。则MySQL将列创建为最小的但足以容纳M字符长的值的TEXT类型。

12)LONGTEXT

最大长度为4,294,967,295或4GB(2**32–1)字符的TEXT列。LONGTEXT列的最大有效(允许的)长度取决于客户端/服务器协议中配置最大包大小和可用的内存。

BLOB和TEXT类型

BLOB 列被视为二进制字符串(字节字符串)。

TEXT列被视为非二进制字符串(字符字符串)。

BLOB列没有字符集，并且排序和比较基于列值字节的数值值。

TEXT列有一个字符集，并且根据字符集的 校对规则对值进行排序和比较。

在TEXT或BLOB列的存储或检索过程中，不存在大小写转换！！！！！

当未运行在严格模式时，如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值，值被截取以保证适合。

如果截掉的字符不是空格，将会产生一条警告。

使用严格SQL模式，会产生错误，并且值将被拒绝而不是截取并给出警告

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB

4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求

可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。

BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR：

1)当保存或检索BLOB和TEXT列的值时不删除尾部空格。(这与VARBINARY和VARCHAR列相同)。

注意：比较时将用空格对TEXT进行扩充以适合比较的对象，正如CHAR和VARCHAR。

2)对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。参见7.4.3节，“列索引”。

3)BLOB和TEXT列不能有默认值。

LONG和LONG VARCHAR对应MEDIUMTEXT数据类型。这是为了保证兼容性。

如果TEXT列类型使用BINARY属性，将为列分配列字符集的二元校对规则。

MySQL连接程序/ODBC将BLOB值定义为LONGVARBINARY，将TEXT值定义为LONGVARCHAR！！！！！

由于BLOB和TEXT值可能会非常长，使用它们时可能遇到一些约束：

1)当排序时只使用该列的前max_sort_length个字节。

max_sort_length的默认值是1024；该值可以在启动mysqld服务器时使用--max_sort_length选项进行更改。

运行时增加max_sort_length的值可以在排序或组合时使更多的字节有意义。

任何客户端可以更改其会话max_sort_length变量的值：

mysql> SET max_sort_length = 2000;

mysql> SELECT id, comment FROM tbl_name

-> ORDER BY comment;

当你想要使超过max_sort_length的字节有意义，对含长值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一种方式是将列值转换为固定长度的对象。

标准方法是使用SUBSTRING函数！！！！！

例如，下面的语句对comment列的2000个字节进行排序：

mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name ORDER BY SUBSTRING(comment,1,2000);

2)BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。

你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序！！！！！

例如，可以使用 mysql和mysqldump来更改客户端的max_allowed_packet值

12)ENUM('value1','value2',...)

枚举类型。只能有一个值的字符串，从值列'value1'，'value2'，...，NULL中或特殊 ''错误值中选出。

ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。

在某些情况下，ENUM值也可以为空字符串('')或NULL：

a)如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。

该字符串与“普通”空字符串不同，该字符串有数值值0。

b)如果将ENUM列声明为允许NULL，NULL值则为该列的一个有效值，并且默认值为NULL。

如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素。

每个枚举值有一个索引：

a)来自列规定的允许的值列中的值从1开始编号。

b)空字符串错误值的索引值是0。这说明你可以使用下面的SELECT语句来找出分配了非法ENUM值的行：

mysql> SELECT * FROM tbl_name WHERE enum_col=0;

c)NULL值的索引是NULL。

如，定义为ENUM的列('one'，'two'，'three')可以有下面所示任何值。还显示了每个值的索引：

值 索引

NULL NULL

'' 0

'one' 1

'two' 2

'three' 3

ENUM列最多可以有65,535个截然不同的值。ENUM值在内部用整数表示！！！！

当创建表时，ENUM成员值的尾部空格将自动被删除！！！！

当检索时，保存在ENUM列的值使用列定义中所使用的大小写来显示(即定义成什么形式，显示为什么形式)。

请注意可以为ENUM列分配字符集和校对规则。对于二进制或大小写敏感的校对规则，当为列分配值时应考虑大小写！！！！

如果在数值上下文中检索一个ENUM值，将返回列值的索引！！！

例如，你可以这样从ENUM列搜索数值：

mysql> SELECT enum_col+0 FROM tbl_name;

如果将一个数字保存到ENUM列，数字被视为索引，并且保存的值是该索引对应的枚举成员！！！！

(但是，这不适合LOAD DATA，它将所有输入视为字符串)。

不建议使用类似数字的枚举值来定义一个ENUM列，因为这很容易引起混淆！！！！

例如，下面的列含有字符串值'0'、'1'和'2'的枚举成员，但数值索引值为1、2和3：

numbers ENUM('0','1','2')

根据枚举成员在列定义中列出的顺序对ENUM值进行排序。(换句话说，ENUM值根据索引编号进行排序)！！！！

例如，对于ENUM('a'，'b')，'a'排在'b'前面，但对于ENUM('b'，'a')，'b'排在'a'前面。

空字符串排在非空字符串前面，并且NULL值排在所有其它枚举值前面！！！！

要想防止意想不到的结果：

按字母顺序规定ENUM列。还可以使用GROUP BY CAST(col AS CHAR)或GROUP BY CONCAT(col)来确保按照词汇对列进行排序而不是用索引数字。

如果你想要确定一个ENUM列的所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE enum_col，并解析输出中第2列的ENUM定义。

13)SET('value1','value2',...)

一个设置。字符串对象可以有零个或多个值，每个值必须来自表创建时规定的允许的一列值'value1'，'value2'，...指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。

SET列最多可以有64个成员！！！！

SET值在内部用整数表示！！！！

例如，指定为SET('one', 'two') NOT NULL的列可以有下面的任何值：

''

'one'

'two'

'one,two'

当创建表时，SET成员值的尾部空格将自动被删除！！！！！！

当检索时，保存在SET列的值使用列定义中所使用的大小写来显示(定义成什么形式，显示为什么形式)。

注意：可以为SET列分配字符集和校对规则。对于二进制或大小写敏感的校对规则，当为列分配值时应考虑大小写！！！！！

MySQL用数字保存SET值，所保存值的低阶位对应第1个SET成员。

如果在数值上下文中检索一个SET值，检索的值的位设置对应组成列值的SET成员。

例如，你可以这样从一个SET列检索数值值：

mysql> SELECT set_col+0 FROM tbl_name;

如果将一个数字保存到SET列中，数字中二进制表示中的位确定了列值中的SET成员！！！！！

对于指定为SET('a','b','c','d')的列，成员有下面的十进制和二进制值：

SET成员 十进制值 二进制值

'a' 1 0001

'b' 2 0010

'c' 4 0100

'd' 8 1000

如果你为该列分配一个值9，其二进制形式为1001，因此第1个和第4个SET值成员'a'和'd'被选择，结果值为 'a,d'。

对于包含多个SET元素的值，当插入值时元素所列的顺序并不重要。在值中一个给定的元素列了多少次也不重要。

当以后检索该值时，值中的每个元素出现一次，根据表创建时指定的顺序列出元素。

例如，假定某个列指定为SET('a','b','c','d')：

SET值按数字顺序排序。NULL值排在非NULL SET值的前面！！！！！

通常情况，可以使用FIND_IN_SET()函数或LIKE操作符搜索SET值：

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

第1个语句找出set_col包含value set成员的行。

第2个类似，但有所不同：它在其它地方找出set_col包含value的行，甚至是在另一个SET成员的子字符串中。

下面的语句也是合法的：

mysql> SELECT * FROM tbl_name WHERE set_col & 1;

mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';

第1个语句寻找包含第1个set成员的值。

第2个语句寻找一个确切匹配的值。

应注意第2类的比较。将set值与'val1,val2'比较返回的结果与同'val2,val1'比较返回的结果不同！！！！！！

指定值时的顺序应与在列定义中所列的顺序相同。

如果想要为SET列确定所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE set_col并解析输出中第2列的SET定义。

Set和ENUM除了存储之外，最主要的区别在于Set类型一次可以选取多个成员，而ENUM则只能选一个！！！！！

数值类型存储需求

列类型 存储需求

TINYINT 1个字节

SMALLINT 2个字节

MEDIUMINT 3个字节

INT, INTEGER 4个字节

BIGINT 8个字节

FLOAT(p) 如果0 <= p <= 24为4个字节, 如果25 <= p <= 53为8个字节

FLOAT 4个字节

DOUBLE [PRECISION], item REAL8个字节

DECIMAL(M,D), NUMERIC(M,D)变长；参见下面的讨论

BIT(M)大约(M+7)/8个字节

DECIMAL(和NUMERIC)的存储需求与具体版本有关：

使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。每个值的整数和分数部分的存储分别确定！！！

每个9位数的倍数需要4个字节，并且“剩余的”位需要4个字节的一部分。

下表给出了超出位数的存储需求：

剩余的 字节

位数 数目

0 0

1 1

2 1

3 2

4 2

5 3

6 3

7 4

8 4

9 4

日期和时间类型的存储需求

列类型 存储需求

DATE 3个字节

DATETIME 8个字节

TIMESTAMP 4个字节

TIME 3个字节

YEAR 1个字节

字符串类型的存储需求

列类型 存储需求

CHAR(M) M个字节，0 <= M <= 255

VARCHAR(M) L+1个字节，其中L <= M 且0 <= M < = 65535(参见下面的注释)

BINARY(M) M个字节，0 <= M <= 255

VARBINARY(M) L+1个字节，其中L <= M 且0 <= M <= 255

TINYBLOB, TINYTEXT L+1个字节，其中L < 28

BLOB, TEXT L+2个字节，其中L < 216

MEDIUMBLOB, MEDIUMTEXT L+3个字节，其中L < 224

LONGBLOB, LONGTEXT L+4个字节，其中L < 232

ENUM('value1','value2',...) 1或2个字节，取决于枚举值的个数(最多65,535个值)

SET('value1','value2',...) 1、2、3、4或8个字节，取决于set成员的数目(最多64个成员)

枚举类型示例：

mysql> create table testenum(gender enum('m','f'));

mysql> desc testenum;

+--------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------+------+-----+---------+-------+

| gender | enum('m','f') | YES | | NULL | |

+--------+---------------+------+-----+---------+-------+

mysql> insert into testenum values

-> ('m'),

-> ('f'),

-> ('2'),#插入对应的第二个值f

-> ('1'),#插入对应的第一个值m，这也说明了，enum类型在内部都是以整数存储的，从1开始

-> (null);

Query OK, 5 rows affected (0.05 sec)

mysql> select * from testenum;

+--------+

| gender |

+--------+

| m |

| f |

| f |

| m |

| NULL |

+--------+

mysql> insert into testenum values ('3');

Query OK, 1 row affected, 1 warning (0.00 sec)#给出警告

mysql> select * from testenum;

+--------+

| gender |

+--------+

| m |

| f |

| f |

| m |

| NULL |

| |

| |

+--------+#可知，超出范围插入枚举类型，会给出警告，并未插入相关值

注意：ENUM类型只允许从值集合中选取单个值，而不能一次取多个值。

mysql> insert into testenum values ('1','2');

ERROR 1136 (21S01): Column count doesn't match value count at row 1#插入两个值会有提示错误

set集合类型示例：

Set类型一次可以选取多个成员，而ENUM则只能选一个

mysql> create table testset(col set('a','b','c','d','e','f','g'));

Query OK, 0 rows affected (0.06 sec)

mysql> desc testset;

+-------+----------------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------------------------------+------+-----+---------+-------+

| col | set('a','b','c','d','e','f','g') | YES | | NULL | |

+-------+----------------------------------+------+-----+---------+-------+

1 row in set (0.01 sec)

mysql> insert into testset values

-> ('a,b'),

-> ('a,f,g'),

-> ('a,g,a');

mysql> select * from testset;

+-------+

| col |

+-------+

| a,b |

| a,f,g |

| a,g |#对于('a,g,a')这样包含重复成员的集合将只取一次，写入后的结果为“a,d”

+-------+

MyISAM存储引擎：建议使用固定长度的数据列代替可变长度的数据列

InnoDB存储引擎：建议使用VARCHAR类型