第 6 章 MySQL的数据类型和运算符

6.1 MySQL数据类型

MySQL支持多种数据类型，主要有：

1. 数值数据类相关：包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点小数类型FLOAT和DOUBLE，定点小数类型DECIMAL。
2. 日期/时间类型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。
3. 字符串类型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。
4. 二进制类型：包括BIT、BINARY、VABINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。

6.1.1 整数类型

整数类型字段属性可以添加AUTO_INCREMENT自增约束条件。
TINYINT（1字节）、SMALLINT（2字节）、MEDIUMINT（3字节）、INT（4字节）、BIGINT（8字节）。
MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度，可使用INT（M）进行设置。显示宽度只是指明MySQL最大可能显示的数值个数，数值的个数如果小于指定的宽度时，显示会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数据依然可以插入，而且显示无误。若不指定，则系统为每一种类型指定默认的宽度值。

6.1.2 浮点数类型和定点数类型

浮点类型和定点类型都可以用（M，D）来表示，其中M（取值范围1~255）为精度，表示总共的尾数；D（1~30，且不能大于M-2）称为标度,表示小数的位数。默认D值为0，M值为10。
MySQL中的小数类型：
FLOAT：单精度浮点数，4个字节。
DOUBLE:双精度浮点数，8个字节。
DECIMAL（M，D），DEC： 压缩的“严格”定点数，M+2个字节。
提示：不论是顶点类型还是浮点类型，如果用户指定的精度值超过精度范围，则会进行四舍五入的处理。

6.1.3 日期与时间类型

1. YEAR：使用单字节表示年份，存储时只需要一个字节。可以使用不同格式指定YEAR的值。
   （1）以4位字符创或者4为数字格式表示。例如输入’2015’或2015.
   （2）以2位字符串格式表示。‘00’~‘69’转换为——2000~2069；‘70~99’转换为——1970~1999。‘0’与‘00’转换值相同，都为2000.
   （3）以2位数字表示。同（2）。不同，0被转换为0000，而不是2000。
2. TIME
   TIME类型用在只需记录时间信息的值，需要3个字节存储，格式为：‘HH：MM：SS’。多种格式指定TIME的值：
   （1）‘D HH：MM：SS’格式的字符串。也可使用以下任何一种“非严格”的语法：‘HH：MM：SS’‘HH：MM’‘D HH：MM’‘D HH’或 ‘SS’。其中D表示日，在插入数据库时，D被转换为小时保存，格式为：‘D*24+HH’。
   （2）‘HHMMSS’格式的、没有分割符的字符串或HHMMSS格式的数值。
   提示：若没有冒号隔开，MySQL解释值的时候，假定最右边的两位表示秒。但是，如果TIME值中使用冒号则一定被当做当天的时间。
   （3）插入当前时间，CURRENT_TIME和NOW（）。
3. DATE类型
   DATE类型用在仅需存储日期值的时候，不存储时间，存储需要3个字节。日期格式为’YYYY-MM-DD’。常用DATE格式如下：
   （1）以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串表示。
   （2）以‘YY-MM-DD’或者‘YYMMDD’表示。
   （3）以YYMMDD数值格式表示。
   使用CURRENT_DATE或者NOW（），插入当年计算机系统的日期。
   提示：MySQL允许不严格的语法插入日期，任何标点符号都可以用作日起部分的间隔符。
4. DATETIME
   DATETIME类型同时包含日期和时间信息，存储需要8个字节。日期格式为‘YYYY-MM-DD HH：MM：SS’。常用DATETIME的格式如下：
   （1）以‘YYYY-MM-DD HH：MM：SS’或者‘YYYYMMDDHHMMSS’字符串格式表示。
   （2）以’YY-MM-DD HH:MM:SS’或者‘YYMMDDHHMMSS’字符串格式表示。
   （3）以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数值格式表示。
   （4）获取当前日期时间函数CURRENT_TIMESTAMP和NOW（）。
   提示：MySQL允许不严格的语法插入日期，任何标点符号都可以用作日期部分或时间部分的间隔符。
5. TIMESTAMP
   TIMESTAMP的显示格式与DATETIEM相同，显示宽度固定在19个字符，存储需要4个字节。TIEMSTAMP列的取值范围小于DATETIME的取值范围为‘1970-01-01 00:00:01’UTC ~ ‘2038-01-19 03:14:07’UTC（世界标准时间）。
   提示：TIMESTAMP和DATETIME除了存储字节和支持的范围不同之外，还有一个最大的区别：DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么；而TIMESTAMP的值的存储是以UTC格式保存，存储时对当前时区进行转换，检索时再转换会当前时区。在进行查询时，根据读者所在的时区不同，显示的日期时间值是不同的。

6.1.4 字符串类型

MySQL支持两类字符串数据：文本字符串和二进制字符串。本节讲文本字符串类型，文本字符串可以进行区分或不区分大小写的比较，也可进行模式匹配查找。

1. CHAR和VARCHAR
   CHAR（M）为固定长度字符串，在定义时指定字符串长度，当保存时在右侧填充空格以达到指定的长度。M的取值范围0~255。当检索CHAR值是，尾部的空格被删除掉。
   VARCHAR（M）是长度可变的字符串，M表示最大的字段长度。M的取值0~65535.VARCHAR的最大实际长度由最长字段的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加1.VARCHAR在值保存和检索时尾部的空格保留。
2. TEXT类型
   TEXT字段保存非二进制字符串。当保存或查询TEXT字段的值时，不删除尾部空格。TEXT类型分为4种：
   （1）TINYTEXT最大长度为255个字符。
   （2）TEXT最大长度为65535个字符。
   （3）MEDIUMTEXT最大长度为16777215个字符。
   （4）LONGTEXT最大长度为4GB个字符。
3. ENUM类型
   是一个字符串对象，其值为表创建时在字段规定中枚举的一列值。语法格式：
   字段名 ENUM（‘值1’，‘值2’， … ，‘值n’）。
   ENUM类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个值。如果创建的成员中有空格，其尾部的空格符将自动删除。ENUM值在内部用整数表示，每个枚举值均有一个索引值，列表值所允许的成员值从 1 开始编号。枚举最多可以有65535个元素。
   提示：ENUM类型字段有一个默认值NULL。若将ENUM列声明为允许NULL，NULL值则为该字段的一个有效值，并且默认值为NULL。若ENUM列被声明为NOT NULL，其默认值为允许的值列的第 1 个元素。
4. SET类型
   是一个字符串对象，可以有零或多个值，SET字段最大可以有64个成员，其值为表创建时规定的一列值。语法格式：
   SET （‘值1’，‘值2’， … ，‘值n’）
   与ENUM不同，SET值在内部用整数表示，列表中每一个值都有一个索引编号。当创建表时，SET成员值的尾部空格将自动被删除。但与ENUM类型的字段不同的是，ENUM类型的字段只能从定义的字段值中选择一个插入，而SET类型的字段可以从定义的列值中选择多个字符的联合。
   如果插入SET字段中的值有重复，则MYSQL自动删除重复的值；插入SET字段的值的顺序不中，MySQL会在存入数据库的时候，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL将忽视这些值，并给出相应警告。

6.1.5 二进制类型

前面讲了文本字符串，本节讲二进制数据的数据类型。

1. BIT类型
   BIT（M）为位字段类型，M表示每个值的位数。取值范围1~64,。若M省略，默认为 1。如果BIT（M）字段分配的值的长度小于M位，在值的左边用0填充。
   使用BIN（）函数将数值转换为二进制。
   提示：默认情况下，MySQL不可以插入超出该字段类型允许范围的值。
2. BINARY和VARBINARY类型
   类似于CHAR和VARCHAR。语法格式：
   字段名称 BINARY（M）或VARBINARY（M）
   其中，BINARY固定长度，指定长度后，不足最大长度的，将在右边填充‘\0’补齐。
   VARBINARY类型的长度是可变的，指定好长度后，其长度可以在0到最大值之间。
3. BLOB类型
   是一个二进制大对象，用来存储可变数量的数据。分为 4 种，存储范围不同。
   TINYBLOB：最大长度255字节；
   BLOB：最大长度35565字节；
   MEDIUMBLOB：最大长度为16777215字节；
   LONGBLOB：最大长度为4GB字节。
   BLOB字段存储的是二进制字符串（字节字符串），TEXT存储的是非二进制字符串（字符字符串）、BLOB字段没有字符集，且排列和比较基于字段值字节的数值，TEXT字段有一个字符集，并根据字符集对值进行排序和比较。

6.2 如何选择数据类型

在选择数据类型时，在可以表示该字段值的所有类型中，应当使用占用存储空间最少的数据类型。

1. 整数和浮点数
   需要小数使用浮点数，不需要使用整数了那些个。
2. 浮点数和定点数
   由于浮点型容易因四舍五入产生误差，对于精度较高时，要使用定点型DECIMAL来存储。
   在数据迁移中，FLOAT（M，D）是非标准SQL定义，容易出问题，最好不使用。另外，两个浮点型数据进行减法和比较预算时，最好使用定点型DECIMAL。
3. 日期和时间类型
   （1）根据实际需要选择能够满足应用的最小存储的日期类型。
   （2）如果要记录年月日时分秒，并且记录的年份比较久远，最好使用DATATIME。
   （3）如果记录的日期需要让不同时区的用户使用，那么最好使用TIEMSTAMP。
4. CHAR和VARCHAR
   对于那些长度变化不大对查询速度要求高的数据可以使用CHAR。在MySQL中，不同的引擎对CHAR和VARCHAR的使用原则有所不同：
   （1）MyISAM存储引擎：建议使用CHAR。
   （2）MEMORY存储引擎：目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR都没有关系。
   （3）InnoDB存储引擎：建议使用VARCHAR。
5. ENUM和SET
   ENUM只能取单值，它的合法取值列表最多允许有65535个成员因此在需要从多个值中取一个时，使用ENUM。
   SET可以取多个值，它的合法取值列表最多允许有64个成员。因此在需要取多个值时，适合使用SET。
6. BLOB和TEXT
   一般保存少量字符串可以选择CHAR或VARCHAR，但是保存大文本时，通常选择TEXT或BLOB。BLOB能用来保存二进制数据，而TEXT只能保存字符数据。使用BLOB和TEXT存在的一些常见问题：
   （1）BLOB和TEXT值会引起一些性能问题，特别是执行了大量的删除操作时。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片处理，避免因“空洞”导致性能问题。
   （2）使用合成索引来提高大文本字段（BLOB或TEXT）的查询性能。
   （3）在不必要的时候避免检索大型的BLOB或TEXT值。
   （4）把BLOB或TEXT列分离到单独的表中。

6.3 常见运算符

常见的运算符有数学计算、比较运算、位运算以及逻辑运算。

6.3.1 运算符概述

1. 算术运算符
   用于各类数值运算，包括加、减、乘、除、求余。
2. 比较运算符
   包括大于、小于、等于、大于等于、小于等于、不等于，以及IN、BETWEEN AND、IS NULL、GREATEST、LEAST、 LIKE、REGEXP等。
3. 逻辑运算符
   有逻辑非、逻辑与、逻辑或、逻辑异或
4. 位操作运算符
   包括位与、位或、位非、位异或、左移、右移。

6.3.2 算术运算符

• 、 -、 *、 /、 %

6.3.3 比较运算符

一个比较运算符的结果总是 1、 0、或者NULL，经常在SELECT查询条件子句中使用，用来查询满足指定条件的记录。

1. 等于运算符“=”
   用来判断数字、字符串和表达式是否相等，相等返回1，否则返回0；
   数值比较时有如下规则：
   （1）若有一个或两个参数为NULL，则比较运算的结果为NULL；
   （2）若同一个比较运算中的两个参数都是字符串，则按字符串进行比较。
   （3）若两个参数均为正数，则按照整数进行比较。
   （4）若一个字符串和一个数字进行相等判断，则MySQL可以自动将字符串转换为数字。
2. 安全等与运算符 “<=>”
   具备“=”的所有功能，唯一不同可以用来判断NULL值。在两个操作数均为NULL时，其返回值为 1，而不是NULL；当其中一个操作数为NULL时，其返回值为0而不是NULL。
3. 不等于运算符“<>”或者“！=”
   用于判断数字、字符串、表达式不相等的判读。若不相等，返回 1；否则返回 0。这两个运算符不能用于判断控制NULL。
4. 小于或等于运算符“<=”
   用来判断左边的操作数是否小于等于右边的操作数。如果小于或等于，返回值为 1，否则返回值为 0。“<=”不能用于判断空值NULL。
5. 小于运算符“<”
   用来判断左边的操作数是否小于右边的操作数。如果小于，返回值为 1，否则返回值为 0。“<”不能用于判断空值NULL。
6. 大于或等于运算符 “>=”
   用来判断左边的操作数是否大于等于右边的操作数。如果大于或者等于，返回值为 1，否则返回值为 0。“>=”不能用于判断空值NULL。
7. 大于运算符“>”
   用来判断左边的操作数是否大于右边的操作数。如果大于，返回值为 1，否则返回值为 0。“>=”不能用于判断空值NULL。
8. IS NULL（ISNULL）、IS NOT NULL运算符
   是检验一个值是否为NULL。
9. BETWEEN AND 运算符
   语法格式：expr BETWEEN min AND max
   假如expr大于或等于min且小于等于max，则返回1，否则返回0；
10. LEAST（value 1，value 2，…）
    语法格式：LEAST（值1，值2，…，值n）
    在有两个或多个参数的情况下，返回最小值。假如任意一个变量为NULL，则返回NULL。
11. GERATEST（value1，value2，…）
    语法格式：GREATEST（值1，值2，…，值n）
    在有两个或多个参数的情况下，返回最大值。假如任意一个变量为NULL，则返回NULL。
12. IN、NOT IN运算符
    IN用来判断操作数是否为IN列表中的一个值，若是返回1，否则返回0。NOT IN 与IN效果相反。
    在左侧表达式为NULL的情况，或是表中找不到匹配项并且表中一个表达式为NULL的情况下，IN的返回值为NULL。
13. LIKE
    用来匹配字符串，语法格式：expr LIEK 匹配条件
    若expr满足匹配条件，则返回1（TRUE）；若不匹配，返回0（FALSE）。若expr或匹配条件中任何一个为为NULL，则结果为NULL。
    LIKE运算符在进行匹配时，可以使用下面两种通配符：
    （1）“%”匹配任意数目的字符，甚至包括零字符；
    （2）“_”智能匹配一个字符。
14. REGEXP
    用来匹配字符串，语法：expr REGEXP 匹配条件
    若expr满足匹配条件，返回1,；若不满足，返回 0； 若expr或匹配条件任意一个为NULL，则返回NULL。
    REGEXP在匹配时，常用以下几种通配符：
    （1）‘^’匹配以该字符后面的字符开头的字符串；
    （2）‘$’匹配以该字符前面的字符结尾的字符串；
    （3）‘.’匹配任何一个单字符；
    （4） “[…]”匹配在方括号内的任何字符。为了命名字符的范围，使用一个‘-’。
    （5）‘’匹配零个或多个在它前面的字符。例如 “.“匹配任意数量的任意字符。

6.3.4 逻辑运算符

在SQL中，所有逻辑运算符的求值所得的结果均为 TRUE、FALSE、NULL。

1. NOT 或者 ！
   表示当操作数为0时，返回 1；为 1 时，返回 0；为NULL是，返回NULL。
2. AND或者&&
   表示当所有操作数均为非零值，并且不为NULL时，返回值为 1；当一个或多个操作为 0 时，返回 0 ；其余情况返回NUL。
3. OR或者 ||
   表示当两个操作数均为非NULL值，且任意一个操作数为非零值时，结果为 1，否则结果为 0；当有一个操作数为NULL，且另一个操作数为非零式，则结果为1，否则结果为NULL；当两个操作数均为NULL时，则所得结果为NULL。
4. XOR
   逻辑异或或运算符XOP。当任意一个操作数为NULL时，返回NULL；对于非NULL的操作数，如果两个操作数都是非0值或者都是 0 值，则返回 0；如果一个为非 0 值，一个为 0 值，则返回1.
   提示：a XOR b等价与（a AND（NOT b））或者（（NOT a）AND b）

6.3.5 位运算符

用来对二进制字节中的位进行测试、位移或者测试处理。

1. 位或运算符 ‘|’
   位或运算符的实质是将参与运算的两个数据，按对应的二进制数进行逻辑或运算。对应的二进制位有一个或两个为1则该为的运算结果为1，否则为0。
2. 位与运算符 ‘&’
3. 位异或运算符‘^’
4. 位左移运算符‘<<’
   左移指定位数之后，左边高位的数值将被移出，右边低位空出的为置零。
5. 位右移运算符‘>>’

6. 位取反运算符‘~’
   提示：MySQL经过位运算之后的数值是一个64位的无符号整数，1 的二进制数表示左右边为 1，其他位均为 0 ，取反操作后，除了最低位，其他位均变为 1。

7. 运算符的优先级
   若运算级别相同，MySQL按表达式的顺序从左到右一次计算。
   优先级顺序（由低到高）：