学习MySQL：MySQL 数据类型

概览

1.在MySQL中,可用的数据类型分为四个主要类别:

（1）数值;

（2）字符;

（3）二进制;

（4）时间;

2.在每个类别中,存在多种特定的数据类型,这些数据类型使用的内存大小和磁盘空间各不相同,因此会对性能产生不同的影响;

3.对于单个记录,为列选择最佳数据类型所产生的性能影响相对较小,但随着数据库的增大,这些较小的影响可能会汇聚成大的影响;应在设计过程中事先考虑这些影响,以免造成性能问题;

4.MySQL还支持“空间扩展”存储引擎功能;通过空间扩展,能够以字符和二进制格式生成,存储和分析地理特征;

5.数据类型的ABC要素:

（1）Appropriate(适当):需要以最适合数据所代表的项的类型来表示数据;

（2）Brief(简洁):选择所用存储空间最少的数据类型。这可节省资源并提高性能;

（3）Complete(完整):选择的数据类型应分配有可存储特定项的最大可能值的充足空间;

数值数据类型

1.数值数据类型类:

（1）整数:整数没有小数部分;即,没有小数位的单个整数值;

（2）浮点数(FLOAT/DOUBLE):表示包含整数部分,小数部分或同时包括二者的近似值数值;此类数据类型使用服务器主机的CPU所用的本机二进制浮点格式(IEEE 754)来表示值;该数据类型用于存储和计算会很高效,但值会存在舍入误差;如果列可为空,则默认值为NULL;如果列不可为空,则默认值为0(数值零);

（3）定点数(DECIMAL):包含整数部分,小数部分或同时包括二者;DECIMAL列中的所有值均包含相同的小数位数,并且完全按给定方式进行存储;DECIMAL值存储起来不如浮点数值高效,但DECIMAL值没有舍入误差,因此更加精确;通常用于存储货币值(其中每个值的精度比其存储大小更加重要);

（4）BIT:BIT列规范规定了一个宽度,指明每个值的位数(1至64位);

2.“精度”和“范围”是适用于浮点值和定点值(这两种类型可以同时包含整数部分和小数部分)的术语:

（1）精度:有效位数;

（2）范围:小数点右侧的位数;

TIPS:可以使用=号跟浮点值进行比较;但由于可能会出现舍入误差,因此结果可能并不总是与预期一样;

字符串数据类型

1.这些类型在以下几个方面有所不同:

（1）数据是以固定长度格式还是可变长度格式存储;

（2）可存储的最大长度;

（3）该类型是否支持非结构化字符串值;

2.为存储字符串数据,MySQL提供了以下数值数据类型存储类:

（1）文本:用于表示真实的字符串数据类型;可以使用此类型存储非结构化且格式自由的字符串(如果其长度符合已定义的空间量);

（2）整数(枚举/集合):用于表示结构化字符串类型;称为“结构化”的原因是,存储在这些类型的列中的值必须通过您所提供的值列表构建,从而定义数据类型;

字符集和排序支持

1.字符串具有以下特征:

（1）序列由特定字符集中的字符组成;

（2）多字节字符集中的每个字符所需的字节数可能是固定的,也可能是可变的;

（3）比较基于对字符串所关联的字符集的排序;

（4）多字节字符比较以字符而不是字节为单位执行;

（5）排序将验证字符的大写版本和小写版本是否等效;

（6）排序将决定同一个字符的不同重音标记是否等效;

（7）排序可以为二进制,其中基于数值字符值进行比较;

2.MySQL具有一个包含大量可供选择的字符集和排序的列表;选择正确与否会对性能有很大的影响;要查看可用的字符集:SHOW CHARACTER SET;

3.使用排序选择可以为相同的字符集选择不同的排序顺序;例如,显示所有latin1字符集:SHOW COLLATION LIKE ‘latin1%’;

二进制字符串数据类型

1.与字符串不同,组成此类二进制字符串值的字节不代表字符;因此,二进制字符串没有附加的字符语义,而且缺少字符串类所表示的字符集和整理信息;

2.在MySQL中,BLOB与TEXT类型非常相似,没有附加的字符集和排序;

时间数据类型

1.YYYY,MM,DD,hh,mm,ss和uuuuuu分别表示“年”,“月”,“日”,“小时”,“分钟”,“秒”和可选的“秒的小数位”;

2.通过为该类型提供一个参数,可声明秒的小数位(可选);例如,TIME(3)是TIME类型,其中秒的小数位部分最多可达三位;

3.TIMESTAMP值采用UTC进行存储(将根据需要转换为当地时间或从当地时间进行转换),其范围为从1970-01-01 00:00:00.000000至2038-01-19 03:14:07.999999;

4.可以定义DATETIME和TIMESTAMP以自动记录当前日期和时间,而不管对行执行INSERT或UPDATE操作的时间为何时;

5.TIMESTAMP与DATETIME的对比:

（1）TIMESTAMP列值的范围比DATETIME列值的范围小,因此存储每个值所需的字节数更少;

（2）通过为不允许出现NULL的TIMESTAMP指定NULL值,可以将其设置为当前日期和时间;

例子:

CREATE TABLE t_time
(
id INT NOT NULL,
t1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
t2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
t3 TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP
);
mysql> INSERT INTO t_time(id) VALUES(1);
mysql> COMMIT;
mysql> SELECT * FROM t_time;
+—-+———————+———————+———————+
| id | t1 | t2 | t3 |
+—-+———————+———————+———————+
| 1 | 2015-08-19 14:00:42 | 2015-08-19 14:00:42 | 0000-00-00 00:00:00 |
+—-+———————+———————+———————+
mysql> UPDATE t_time SET t1 = ‘2000-01-01 00:00:00’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t_time;
+—-+———————+———————+———————+
| id | t1 | t2 | t3 |
+—-+———————+———————+———————+
| 1 | 2000-01-01 00:00:00 | 2015-08-19 14:00:42 | 2015-08-19 14:01:43 |
+—-+———————+———————+———————+
1 row in set (0.00 sec)

空间数据类型

MySQL实现了由开放地理空间协会(Open Geospatial Consortium, OGC)提出的“包含几何类型的SQL”环境的子集:

1.用于单个几何体值:

（1）GEOMETRY:层次结构的根类,任何类型的值;

（2）POINT:坐标空间中的单个位置;

（3）CURVE:一维几何体,由点组成的序列;

（4）LINESTRING:点之间具有线性内插的曲线;

（5）SURFACE:二维几何体;

（6）POLYGON:表示多边几何体的平面;

2.用于保存几何体值的集合:

（1）MULTIPOINT:Point元素;

（2）MULTICURVE:Curve元素;

（3）MULTILINESTRING:LineString元素;

（4）MULTISURFACE:Surface元素;

（5）MULTIPOLYGON:Polygon元素;

（6）GEOMETRYCOLLECTION:任意类型的几何体;

将数据类型设置为NULL

1.在SQL中,对表达式求值的结果可以为null;null值是一个特殊的值,表示值无法进行计算或未知;

2.何时使用NULL:在数据库设计的开始阶段,当列出要包含在内的数据时,发现某些数据可能不适用于所有列;

3.何时不应使用NULL:在某些情况下,不应允许列中使用null值;

（1）最常见的情况即该列为主键时;

（2）另一个例子是:当列必须包含值时,数据库设计才有意义;

（3）NULL值不保存到索引中;

长按下图二维码关注“AIX专家俱乐部”公众号