MySQL数据库（三）—MySQL数据库数据类型和运算符

一、 MySQL数据类型介绍

MySQL支持多种数据类型，主要有数值类型、日期类型、时间类型和字符串类型。

整数类型

数据类型主要用来存储数字，MySQL提供多种可供选择的整数类型，用整数作为数据类型的字段可以设置auto_increment自增约束条件.

浮点和定点数据类型

MySQL中使用浮点数和定点数来表示小数。浮点有分为单浮点和双浮点。

日期与时间类型

（1） YEAR

YEAR类型是一个单字节类型，用于表示年，在存储时只需要1个字节。可以使用各种格式指定YEAR值，如下所示：

• 以4位字符串或者4位数字格式表示的YEAR，范围为1901~2155。输入格式’YYYY’或者YYYY。

• 以2位字符串格式表示的YEAR，范围为00到99。00~69转换为2000~2069；70~99转换为1970~1999；

• 以2位数字表示的YEAR值，范围为1~99。1~69值是2001~2069；70~99范围值转换为1970~1999，而在这里0值被转换为0000；
  

（2） TIME

TIME类型用于表示时间信息，在存储时需要3个字节。表示某一事件过去的时间和两个事件之间的时间间隔。可以大于24甚至为负值。 ·

• ‘D HH:MM:SS’格式的字符串。有’HH:MM’，‘D HH:MM’ ,‘D HH’ ,‘SS’。D表示日，可以取0~34之间的值。在插入数据库时D被转换为小时保存（D*24+HH）。

• 'HHMMSS’格式的数值，会自动分成HH:MM:SS但各自有取值范围，超过则输入不合法。
  

（3） DATE

DATE只用于输入日期，没有时间，在存储时需要3个字节。日期格式为：‘YYYY-MM-DD’ 。

• 以’YYYY-MM-DD’或者’YYYYMMDD’字符串格式表示日期，取值范围是’1000-01-01’~‘9999-12-03’。

• 以’YY-MM-DD’或者’YYMMDD’字符串格式表示日期，取值范围与年数相关，00~69年值范围是2000~2069，70~99年值范围是1970~1999。

• 以YY-MM-DD或者YYMMDD数字格式表示日期，取值范围与年数相关，00~69年值范围是2000~2069，70~99年值范围是1970~1999。

• 使用current_date或者now()，插入当前系统日期。
  

（4） DATETIME

DATETIME用于同时输入日期和时间信息，在存储时需要8个字节。格式为：’’YYYY-MM-DD HH:MM:SS’, 只要符合格式使用字符串和数字类型均可。

• 以’YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’字符串格式表示日期，取值范围是’1000-01-01 00:00:00’~‘9999-12-03 23:59:59’ 。

• 以’YY-MM-DD HH:MM:SS’或者’YYMMDDHHMMSS’字符串格式表示日期，取值范围与年数相关，00~69年值范围是2000~2069，,70~99年值范围是1970~1999。

• 以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示日期，取值范围与年数相关，00~69年值范围是2000~2069，,70~99年值范围是1970~1999。

• 使用now()，插入当前系统日期时间。
  

（5） TIMESTAMP

TIMESTAMP的显示格式与DATETIME相同，显示宽度是19个字符。存储需要4个字节，格式为：‘YYYY-MM-DD HH:MM:SS’ 。但是TIMESTAMP列的取值范围小于DATETIME的取值范围，为’1970-01-01 00:00:01’UTC~‘2038-01-19 03:14:07’ UTC。其中UTC（Coordinated Universal Time）世界标准时间，因此在插入数据时要保证在合法取值范围内。

字符串类型

字符串类型除了可以存储字符串数据以外，还可以存储其他数据，比如图片、声音和二进制数据。字符串可以进行区分或或者不区分大小写的串比较。

MySQL中的字符串char、varchar、binary、varbinary、blob、tinytext、text、enum和set。

（1） char和varchar类型

char(M)为固定长度字符串，在定义时要指定字符串列长。当保存时在右侧填充空格以达到指定长度。M表示列长度，当检索时实际值尾部空格会自动删除。

varchar(M)为长度可变的字符串，M表示列最大长度，但是保存时实际占用空间为字符串实际长度加1。不管是保存还是检索varchar的值时实际值尾部的空格都会保留。

（2） text类型

text列保存非二进制字符串，如文章、评论等等。当保存和查询text列值时，不删除尾部空格。Text类型分为四种：tinytext、text、mediumtext和longtext。不同的text类型存储空间和数据长度不同。

• tinytext：最大长度255（28~1）个字符的text列。

• text：最大长度为65535（216~1）个字符的text列。

• mediumtext：最大长度为16777215（224~1）个字符的text列。

• longtext：最大长度为4294967295或4GB（232~1）个字符的text列。

（3） enum类型

enum是一个字符串对象，其值为创建时在列规定中枚举的一列值。Enum类型的字段取值时只能在指定的枚举列表中取，而且一次只能取一个。如果创建的成员中有空格，则其尾部的空格将自动删除。Enum值在内部用整数表示，每个枚举值均有一个索引值：列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。枚举最多可以有65535个元素。

Enum 值依照列索引顺序排列，并且空字符排在非空字符串之前，null值排在其他所有枚举值之前。

4） set类型

set类型是一个字符串对象，可以有一个或者多个值，set列最多可以有64个成员，其值为创建表时规定的一系列值。Set成员各值之间用逗号,隔开。语法格式如下：

set(‘值1’,‘值2’,…‘值n’)

与enum类型相同，set的值在内部用整数表示，每一个值都有一个索引编号。当创建表时，set成员值得尾部空格将自动删除。但与enum类型不同的是，enum类型的字段只能从定义的列值中选择一个插入，而set类型的列可以从定义的列值中选择多个字符联合插入。

如果插入set字段的列值有重复，则MySQL自动删除重复值；插入set字段值顺序并不重要，MySQL会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL将忽视这些值，并给出警告。

二进制类型

MySQL支持两类字符型数据：文本字符串和二进制字符串。前面讲解了存储文本的字符串类型，今天将讲解MySQL中存储二进制数据的数据类型。

MySQL中的二进制数据类型有：bit、binary、varbinary、tinyblob、blob、mediumblob和longblob.

(1) bit类型

bit是位字段类型。M表示值得位数，范围为1~64。如果M被省略，默认值为1。如果为bit(M)列分配的值长度小于M位，在值得左边用零填充。如bit(5)列分配一个值b’101’，其效果与分配b’00101’相同。Bit数据类型用来保存位字段值，如以二进制的形式保存数据13,13的二进制为1101，在这里需要位数至少4位的bit类型，既可以定义列类型为bit(4)。大于位数(M)的二进制数是不能插入bit(M)类型字段中的。

(2) binary和varbinary类型

binary和varbinary类型类似于char和varchar，不同的是他们包含二进制字节字符串。使用语法如下：

列名称
binary(M)或者varbinary(M)

Binary的长度是固定的，指定长度之后，不足最大长度的，将在右边填充“\0”补齐以达到指定长度（M）。

Varbinary类型的长度是可变的，其长度可以在0到最大值之间。如制定列数据类型为varbinary(20)，如果插入的值得长度只有10，则实际存储空间为10加1，即其实际占用的空间为字符的实际长度加1。


可以看到b字段的数据长度为3，而vb字段的数据长度仅为插入的一个字符长度1。B字段和vb字段的长度截然不同，因为b字段不足的空间填充了"\0"，而vb字段则没有填充。

(3) blob类型

blob是一个二进制的大对象，用来存储可变数量的数据。Blob类型分为四种：tinyblob、blob、mediumblob和longblob，他们可以容纳的最大长度不同。

Blob列存储的是二进制字符串（字节字符串）；text列存储的是非二进制字符串（字符字符串）。Blob列没有字符集，并且排序和比较基于列值字节的数值，text列有一个字符集，并且根据字符集对值进行排序和比较。

二、 选择数据类型的方法

MySQL提供大量的数据类型，为了优化存储，提高数据库性能，在任何情况下都应使用最精确的类型。即选择占用存储空间最少的类型。

1. 整数和浮点数

有小数用浮点数，没有就用整数。但注意浮点数存储小数时会自动进行四舍五入。浮点包括float和double。double比float要求的精度更高，所以当要求存储精度高时用double。

2. 浮点数和定点数

浮点数float和double相对于定点数decimal来说优点是：在长度一定的情况下浮点数表示的范围更大。但由于浮点数容易产生误差，因此，如果对精度要求比较高则用decimal来存储。

Decimal在MySQL中是以字符串存储的，用于定义货币等对精度要求比较高的数据。注意定点数decimal标准定义格式只有decimal(M,D)，其他的像float(M,D)是不标准的，在数据库迁移时会出问题。

3. 日期与时间类型

仅仅记录年份用year即可，记录时间time即可，都记录timestamp和datetime都可以，但如果存储较大日期用相对范围大的datetime类型。

但是如果插入记录同时需要记录插入记录时的时间，那么使用timestamp是最方便的。

4. char与varchar

char存储固定长度字符，varchar存储可变长度字符。Char会自动删除插入数据的尾部空格，varchar不会删除尾部空格。

char采用固定长度，所以它的处理速度比varchar要快，但它的缺点就是浪费空间。所以随存储长度不大，但在检索速度上有要求的数据可以使用char类型，反之可以使用varchar来实现。

对于MyISAM存储引擎，最好使用固定长度的数据代替可变长度，这样可以使整个表静态化，从而使数据检索更快，用空间换时间。

对于InnoDB存储引擎，使用可变长度数据列。因为InnoDB数据表的存储区格式不分固定长度和可变长度，由于varchar按实际长度存储节省空间，所以对磁盘I/O和独具存储总量说比较好。

5. enum和set

enum只能取单值，他的数据列是一个枚举集合。它的合法取值列表最多允许有65535个成员。因此，在需要从多个值中选取一个时，可以使用enum。例如性别字段适合定义为enum类型。

Set可以取多个值。它的合法取值列表最多允许有64个成员。空字符串可是一个合法的set值。在需要取多个值时适合使用set类型。如一个人的兴趣爱好。

Enum和set的值是以字符串的形式出现的，但在内部，MySQL以数值的形式存储它们。

6. blob和text

blob是二进制字符串，text是非二进制字符串，两者均可存放大量的信息。Blob主要存储图片、音频信息等，而text只能存储纯文本文件。

三、 常见运算符介绍

运算符是告诉MySQL执行特殊算术或逻辑操作的符号。MySQL的内部运算符很丰富，主要有四大类：算数运算符、比较运算符、逻辑运算符和位操作运算符。

1.运算符概述

a) 算术运算符

算术运算符用于各种数值运算，包括加(+)、减(-)、乘(*)、除(/)、求余(或称模运算，%)。

b) 比较运算符

比较运算符用于比较运算，包括大于(>)、小于(<)、等于(=)、大于等于(>=)、小于等于(<=)、不等于(!=)以及IN、BETWEEN AND、IS NULL、GREATEST、LEAST、LIKE、REGEXP等。

c) 逻辑运算符

逻辑运算符的求值结果均为1(TRUE)、0(FALSE)，这类运算有逻辑非(NOT或者！)、逻辑与(AND或者&&)、逻辑或(OR或者||)、逻辑异或(XOR)。

d) 位操作运算符

参与运算的操作数，按二进制位进行运算。包括位与(&)、位或(|)、位非(~)、位异或(^)、左移(<<)、右移(>>)6种。

2.算术运算符

3.比较运算符

4.逻辑运算符

在SQL中所有的逻辑运算符的求值结果均为TRUE，FALSE或NULL。在MySQL中体现为1(TRUE)、0(FALSE)和NULL。

5.位运算符

6.运算符的优先级

运算符的优先级决定了不同运算符在表达式中计算的先后顺序，下表给出了各类运算符的优先级，越靠前优先级越高，但一般我们采用圆括号””()”来改变优先级顺序。