MySQL数据类型

MySQL数据类型

MySQL中支持的数据类型主要有以下几种，可以? data types 命令查看数据类型

-------------------------------------

关于整形

整形：TINYINT , SMALLINT , MEDIUMINT , INT , INTEGER , BIGINT   //所占字节分别是1，2，3，4，8个字节

浮点型： FLOAT , DOUBLE   //所占字节分别是4个，8个字节

定点型：DEC(M,D) , DECIMAL(M,D)  //所占字节M+2个

位类型：BIT(M)   // 所占字节1~8个

1、若插入的数据值大于最大值或者小于最小值，则会报 Out of range 错误。

2、int型默认为11位宽度，若指定宽度n(n<11)后，插入的数据位数＞n，则不会发生数据截断，此时指定的宽度位数失去意义。

3、所有的整形都有一个可选属性unsigned，指定unsigned后，只能表示非负数，最小0，最大是原来的2倍。

4、zerofill配合指定宽度使用，最左位补0.如： create table teblename(id int(5) zerofill); 如果一个列指定为zerofill，那么MySQL自动为该字段添加UNIQUE属性。

5、整形还有一个属性auto_increment ,可用于自增，一个表最多只能有一个整形字段设置为auto_increment。对于任何想使用auto_increment的列，应定义为NOT NULL，并定义为PRIMARY KEY或者定义为UNIQUE键。

如：

CREATE TABLE tablename(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY);

CREATE TABLE tablename(id INT AUTO_INCREMENT NOT NULL ,PRIMARY KEY(id));

CREATE TABLE tablename(id INT AUTO_INCREMENT NOT NULL ,UNIQUE(id));

-----------------------------------

关于小数

表示小数：浮点数和定点数

浮点数：单精度float和双精度double，定点数只有一种decimal

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

float(M,D) : 表示单精度浮点数能表示M位数，D位小数，那么整数位为M-D位 ，超出的部分会发生精度损失，MySQL采用四舍五入的方式保存。

float和double在不注明精度的情况下，会采用默认精度，而decimal不指定精度的时候，默认整数位数10，小数位数0。

注意一点，浮点数后面跟"(M,D)"的用法非标准用法，在数据库迁移的时候应当注意。

当操作提示warning时，可以使用show warning命令查看警告信息。

---------------------------------

对于BIT（位）类型，使用select 查看结果为空应当使用bin()或hex()查看

---------------------------------

关于date类型

MySQL中表示date类型的有DATE、DATETIME、TIMESTAMP、TIME、YEAR  //分别所占字节数为4个、8个、4个、3个、1个

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

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

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

如果要经常插入更新时间为系统时间，则用TIMESTAMP，注，最大表示年份为2038年，默认值是CURRENT_TIMESTAMP，且每张表只能有一个列设置为该默认值。

如果只是表示年份，则用YEAR

--------------------------------

关于字符串类型

CHAR与VARCHAR的区别

CHAR列的长度固定为创建表时声明的长度，可以是0~255之间的任何值。而VARCHAR列中的值的长度为可变长字符串，长度可以是0~255（5.0.3版本之前的）或者65535（5.0.3版本之后的）的任意值。在检索的时候CHAR删除了尾部的空格，而VARCHAR则保留了尾部的空格。VARCHAR所占长度总是比定义长度+1个字节，用于表示可变长的长度值。char可以提高速度，却费空间，varchar相反。并且不同的存储引擎对于CHAR与VARCHAR的使用原则是不一样的，InnoDB建议使用VARCHAR。

----------------------------------

关于ENUM枚举类型

可以在创建表的时候指定枚举类型，它所占字节由枚举的个数决定，0~255占一个字节，255~65535占两个字节，最多有65535个枚举

如，CREATE TABLE t (gender ENUM('M','F'));

插入数据，INSERT INTO TABLE t VALUES('M'),('1'),('f'),(NULL);  

---------------------------------

关于Set类型

Set和ENUM非常相似，也是一个字符串对象，可以包含0~64个成员，所占字节由 成员数/8 向上取整 决定，1~8占一个字节，9~16占两个字节，17~24占3个字节

如，create table t2(col set ('a','b','c','d'));    insert into t2 values('a,b'),('a,a'); 实际插入两行数据分别是a,b和a，重复的元素被去掉，且只允许输入指定的数据。

---------------------------------

关于TEXT和BLOB

CHAR和VARCHAR只能用于保存少量的字符串，对于大量的文本则选择TEXT或者BLOB，二者的区别在于BLOB可以保存二进制数据，可以用来保存图片，而TEXT只能保存字符数据。

BLOB和TEXT会引起一些性能问题，特别是执行删除操作的时候会产生大量的空洞，为了提高性能，建议定期执行OPTIMIZE TABLE功能优化整理碎片。

对于BLOB和TEXT的精确查找可以使用合成索引，在数据库中增加一列索引值，比如MD5(TEXT)，最后根据索引值进行精确查找。

应该尽可能的将TEXT和BLOB与主表分离，或者少使用SELECT * 检索，这将导致非常大的开销。