MySQL的数据类型简介

MySQL数值类型

 

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

 

数值类型：

    MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

    关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

    BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

    作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

 

 

    BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1~64，默认为1。

 

    BIT其实就是存入二进制的值，类似010110。

 

    如果存入一个BIT类型的值，位数少于M值，则左补0.

 

    如果存入一个BIT类型的值，位数多于M值，MySQL的操作取决于此时有效的SQL模式：

 

    如果模式未设置，MySQL将值裁剪到范围的相应端点，并保存裁减好的值。

    如果模式设置为traditional(“严格模式”)，超出范围的值将被拒绝并提示错误，并且根据SQL标准插入会失败。

    下面是官方示例：

 

mysql> CREATE TABLE t (b BIT(8));

mysql> INSERT INTO t SET b = b'11111111';

mysql> INSERT INTO t SET b = b'1010';

mysql> INSERT INTO t SET b = b'0101';

mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t;

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

| b+0 | BIN(b+0) | OCT(b+0) | HEX(b+0) |

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

| 255 | 11111111 | 377 | FF |

| 10 | 1010 | 12 | A |

| 5 | 101 | 5 | 5 |

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

 

整数类型：

    在整数类型中，按照取值范围和存储方式不同，分为tinyint、smallint、mediumint、int和bigint这5中类型。如果超出类型范围的操作，会发生“out of range”错误提示。为了避免此类问题发生，在选择数据类型时要根据应用的实际情况确定其取值范围。

 

下面介绍帮助中的数据类型语法：

 

上图中的几个属性：

UNSIGNED：

    无符号。一个数值如果无（“-”）符号，也就是说这个数值是“整数”。如果需要在字段中保存非负数或者需要较大的上限值时，可以用此选项。它的取值范围是正常值的下线取0，上限取原值的2倍。

 

（length）：

    指定显示宽度，例如int(5)表示当数值宽度小于5位的时候在数字前面填满宽度。如果未指定则默认为11。一般配合zerofill（“O”填充）使用。

 

ZEROFILL：

    顾名思义，即“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。下面几个例子分别描述了填充前后的区别：

 

1、创建t1表，表中两个字段id1和id2，宽度分别为11和5

从上图可知，显示的格式没有异常。

 

2、分别修改id1和id2的字段类型，加入zerofill参数：

 

解惑：

    设置了宽度限制后，如果插入大于宽度限制的值，会不会截断或者插不进去报错呢？

    不会对插入的数据有影响，还是按照数值类型的实际精度进行保存。这时宽度格式实际已经没有意义，左边不会再填充任何“0”字符。

    另外还有一个属性，AUTO_INCREMENT。在需要产生唯一标示符或顺序值时，可利用此属性，这是属性只用于整数类型。它有以下特点：

    1、它的值从1开始，每行增加1。

    2、在插入NULL到一个AUTO_INCREMENT列时，MySQL插入一个比该列中当前最大值大1的值。

    3、一个表中最多只能由一个AUTO_INCREMENT列。

    4、对于任何想要使用AUTO_INCREMENT的列，应该定义为NOT NULL，并定义为PRIMARY KEY或定义为UNIQUE键。

    可使用下列任何一种方式定义AUTO_INCREMENT:

 

 

浮点数和定点数：

    

    对于小数的表示，MySQL分为两种方式：浮点数和定点数。浮点数包括float和double，定点数只有decimal一种表示。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等高精度的数据,浮点数不精确。

    浮点数和定点数都可以用类型名称为加“（M,D）”的方式来表示。“（M,D）”表示该值一共显示M位数字（整数位+小数位，即全长）， D表示小数点后面的位数。M和D又称精度和标度。

    值得注意的是，浮点数后面跟（M,D）的用法是非标准用法，如果要进行数据库迁移，最好不要这么使用。Float和double不指定精度时，默认会按照操作系统和实际硬件决定的来显示，而decimal在不指定精度时，默认按照整数位为10，小数位为0，即decimal(10,0)。

例如：

创建t2表

 

向三个列分别插入数值1.234，遭遇了一个警告信息：

 

查看表中数据，验证如果浮点数不写精度和标度，会按照实际的精度值来显示，对于定点数，则会按照默认值decimal（10，0）来处理，并且系统会报错。

另外注，对于浮点数，如果写了精度和标度，但插入的值超出，则会自动将四舍五入后的结果插入，系统不会报错。

 

 

其他比较容易理解的解释：

MySQL中存在float,double等非标准数据类型，也有decimal这种标准数据类型。

其区别在于，float，double等非标准类型，在DB中保存的是近似值，而Decimal则以字符串的形式保存数值。

float(M,D) M为全长，D为小数点后长度。对于不精准的例子，如下：

mysql> create table t1(c1 float(10,2), c3decimal(10,2)); 

Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(9876543.21, 9876543.12);                   

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;                        

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

| c1                | c3                   |

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

| 9876543.00 | 9876543.12 | 

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

2 rows in set (0.00 sec)

 

 

 

 

 

日期和时间类型：

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

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

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

 

注意：

如果只表示年份，则可以用YEAR来表示，它比DATE占用更少的空间。

 

DATE/TIME/DATETIME是最常使用的3种日期类型，来看下面的小例子：

 

TIMESTAMP:

    时间戳。它跟DATETIME都可以表示日期，但有不同。如果要插入或更新日期为当前系统时间，则通常使用TIMESTAMP来表示。它有下列特性，请注意：

1. MySQL只给表中的第一个TIMESTAMP字段设置默认值为系统日期，如果有日二个TIMESTAMP类型，则默认值为0值。
2. TIMESTAMP的另外一个特性是它和时区相关。当插入日期时，会先转换为本地时区后再存放；从数据库里取出时，也同样将日期转换为本地时区后显示。

 

特性一实验：

1、创建t2表，输入一个null值，然后查看是否能将本地时间插入到表中。

 

2、增加ts1列：

释义：后增加的ts1列的默认值是0，而非CURRENT_TIMESTAMP。如果强制修改为CURRENT_TIMESTAMP，系统就会报错。

 

特性二实验：

1、创建t3表，ts2列使用的日期类型是datatime只是为了对比。

2、查看当前时区，因为我在北京，SYSTEM为东八区时间（+8：00）

3、用now()函数插入当前日期

4、修改时区为东京时间（东九区），并再次查看表中的时间。

    可以看出，timestamp更能反映出实际的日期，而datetime则只能反映出插入时当地的时间。

 

 

 

字符串类型：

 

CHAR和VARCHAR对比

1．CHAR的长度是固定的，而VARCHAR的长度是可以变化的（需CPU计算）， 比如，存储字符串“abc"，对于CHAR (10)，表示你存储的字符将占10个字节(包括7个空字符)，而同样的VARCHAR (10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。 

2．CHAR的效率比VARCHAR的效率稍高。

 

CHAR与VARCHAR2是一对矛盾的统一体，两者是互补的关系. 

VARCHAR比CHAR节省空间，在效率上比CHAR会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的‘以空间换效率’。 

VARCHAR虽然比CHAR节省空间，但是如果一个VARCHAR列经常被修改，而且每次被修改的数据的长度不同，这会引起‘行迁移’(Row Migration)现象，而这造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用CHAR代替VARCHAR会更好一些。

 

Varchar、TEXT、BLOB都是变长字符串

TEXT存放纯文本字符串，不能存放图片

BLOB被视为二进制字符串，可以存放图片。

 

枚举类型：ENUM         enumerate，列举、枚举

是字符串类型的一种，它的取值范围需要在创建表时通过枚举方式显示指定。

 

SET类型

SET类型和ENUM类型非常相似，也是一个字符串对象。最主要的区别在于SET类型一次可以选取多个成员，而ENUM则只能选一个。