学习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值不保存到索引中;

 

学习MySQL:体系结构及相关概念

学习MySQL:系统管理

学习MySQL:服务器配置

学习MySQL:客户机和工具