您现在的位置是：首页 > 数据库 > MySQLMySQL

数据类型和运算符

第十三双眼睛2019-06-05【MySQL】人已围观

简介数据库表由多列字段构成，每一个字段可以指定不同的数据类型，指定字段的数据类型之后，也就指定了向字段中插入数据的内容，例如，当用存储数值的时候，可以将他们存储为整数类型，也可以将他们存储成为字符串类型，不同的数据类型决定了他们存储时的方式，本章主要介绍MySQL中的数据类型和常见的运算符

MySQL数据类型介绍
MySQL支持的数据类型很多，主要包括数值型，日期时间型，字符串类型
1数值型：数值型数据主要包括TINYINT,SMALLTIN,MKEDIUMINT,INT,BIGINT,浮点型数据包括FLOAT,DOUBLE两种，定点小数类型DECIMAL.
2日期时间型：包括YEAR,TIME,DATE,DATETIME,TIMESTAMP.
3字符串类型：包括CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,SET.字符串类型又分为文本字符串和二进制字符串类型。

整数类型
数值型数据类型主要用来存储数字，MySQL提供了多种数值类型，不同的数据类型有不同的取值范围，可以存储的值越大，需要占用的内存空间也越大，MySQL提供的整数类型包括以下几种：TINYIINT,SMALLINT,MEDIUNINT,INT,BIGINT.以下是他们占用的内存字节数：
TINYINT:1个字节
SMALLINT：2个字节
MEDIUMINT：3个字节
INT：4个字节
BIGINT8：个字节
整数类型的字段上可以加自增加属性
在创建表的过程中，在整型字段后指定的数字，表示的是该数据的显示宽度，例如声明一个字段YEAR INT(4),表名此字段的显示宽度是4，此时插入一条数据，该字段是19999，是可以正常插入的，查询显示的时候，显示的19999，而不是只有4位数字。也就是说，只有数据本来的位数小于定义的位数时，才会起作用。如果定义时不指定显示宽度，则系统会位每一种数据类型指定默认的显示宽度，默认的显示宽度以能够显示该类型最大值位准

浮点数类型和定点数类型
MySQL中使用浮点数和定点数来表示小数，浮点类型有两种，单精度类型FLOAT和双精度类型DOUBLE,定点数类型只有一种，浮点类型和定点类型都可以用（M,N）来表示，其中M表示精度，代表总共的数据位数，N称为标度，代表小数的位数。
FLOAT:4个字节
DOUBLE:8个字节
MECIMAL(M,D)：M+2个字节
DECIMAL是以串存放的，它的存储空间不是固定的，而是由精度决定，占用M+2个字节。
FLOAT和DOUBLE不指定精度时，默认会按照实际的精度，DECIMAL如果没有指定精度，默认为（10，0）。
浮点数相对于定点数的优点时在长度一样的情况下，浮点数能够表示更大的数据范围，它的缺点是会引起精度问题。

日期与时间类型
MySQL有多种表示日期的类型，主要有DATETIME,DATE,TIMESTAMP,TIME,YEAR,当只记录年的时候，就可以用YEAR，而没有比要使用DATE.

YEAR:占用1个字节来存储，可以使用不同的格式来指定YEAR值：
1：以4位字符串或者4位数字来表示YEAR,范围是1901-2155，超过范围的值无法插入，会报错。
2：以2位字符串格式表示YEAR,范围位00-99，00-69和70-99范围内的值会被分别转换为2000-2069和1970-1999内的值，插入超过取值范围的值将被转换为2000
3：以2位数字表示的YEAR,范围是1-99，1-69和70-99范围内的值分别被转换为2001-2069和1970-1999内的值，非法值会被转换为0000.而不是2000。
如：创建表CREATE TABLE tmp3(y YEAR);
然后向表中插入数据，INSERT INTO tmp3 values(2010),('2010')
再向表中插入数据，INSERT INTO tmp3 values(266);
那么，前一条语句是可以正常插入的，但是后面一条不行，会报错，因为最大允许的值是2155。

TIME
TIME类型适用于只需要表示时间的值，存储时需要3个字节，格式为HH:MM:SS,HH表示小时，MM表示分钟，SS表示秒。
如果为TIME分配简写值时要注意，如果没有冒号，MySQL解释值时，会把最右边的看作秒，比如，1122会看作00：11：22，而不是11：22：00，如果有冒号的话，最左边的会被看作时小时，比如11：22表示11：22：00.

DATE
DATE类型用在仅需要日期不需要时间时，存储时需要3个字节，格式为YYYY-MM-DD,YYYY表示年，MM表示月，DD表示天，在给日期类型的字段赋值时，可以使用字符串类型或者数字类型，只要符合日期的格式即可。
1使用‘YYYY-MM-DD’格式的字符串，取值的范围为‘1000-01-01’到‘9999-12-31’，如，输入2012-12-31，或者2012-12-31，插入数据库以后都是2012-12-31.
2以格式‘YY-MM-DD’或者YY-MM-DD格式的字符串或者数字来表示，其中YY的范围在0-69时表示2000-2069，当YY的范围时70-99时，表示1970-1999.
3以CURRENT_DATE或者NOW(),表示当前时间，CURRENT_DATE只返回当前时间的日期部分，没有时间部分，NOW()返回了当前时间的全部，在保存到数据库的时候，只保留了日期部分。
MySQL允许不严格的语法，任何标点符号都可以作为日期部分的分隔符，比如，98-11-31，98.11.31，98/11/31，98@11@31都可以正确的插入数据库中。

DATETIME
DATETIME类型用在需要同时包含日期和时间的时候，存储的时候需要8个字节，格式为‘YYYY-MM-DD HH:MM:SS',在给DATETIME类型的字段插入数据时，可以使用字符串或者数字类型。
1以‘YYYY-MM-DD HH:MM-SS’或者‘YYYYMMDDHHMMSS’表示时间时，范围为‘1000-01-01 00：00：00’到‘9999-12-31 23：59：59’
2以‘YY-MM-DD HH:MM:SS’或者‘YYMMDDHHMMSS’字符串表示。
3以 YYYYMMDDHHMMSS或者YYYYMMDDHHMMSS数字格式来保存。
4使用NOW()函数来插入数据库

TIMESTAMP
TIMESTAMP与DATETIME相同，显示宽度为19位，但是存储时需要4个字节，TIMESTAMP的取值范围小于DATETIME的取值范围，为'1970-01-01 00:00:01'到
‘2038-01-19 03：14：07’。
TIMESTAMP与DATETIME在存储字节不同，和表示范围不同，还有一个区别，DATETIME在存储数据时，按实际输入的格式进行存储，输入什么就存储什么，与时区无关，而TIMESTAMP的话，存储时先对当前时区进行转换，查询时再转换为当前时区，当前时区不同，显示的结果也不同。

文本字符串类型
字符串类型用来存储字符串类型，除了可以存储字符串数据之外，还可以存储其他数据，比如图片，声音等，MySQL支持两种字符串。文本字符串，和二进制字符串。
CHAR和VARCHAR类型
CAHR为固定长度字符串，再定义时指定字符串长度，当保存时，再右侧填充空格以达到指定长度，范围为0-255。
VARCHAR是长度可以改变的字符串，可以定义最大长度，范围为0-65535，VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，实际占用的空间为字符串长度加1，如，定义了一个VARCHAR(10),如果插入了5个字符，那么实际存储的字符串为5个字符加一个结束标志。

TEXT类型
TEXT类型用来保存非二进制字符串，如文章，评论。TEXT分为4种，TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT.不同类型的TEXT存储空间不同，数据长度也不同
TINYTEXT的最大长度为255
TEXT的最大长度为65535
MEDIUMTEXT的最大长度为2的24次幂
LONGTEXT的最大长度为2的32次幂

ENUM类型
ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值，具体语法为：字段名 ENUM ('值1'，‘值2’，...,'值n'),ENUM类型的字段在取值时，只能在枚举的列表中取值，而且一次只能取一个，ENUM值在内部用整数表示，每一个值都有一个索引值，枚举最多可以有65535个值。ENUM总有一个默认值，如果声明ENUM列可以为空，则默认值就是NULL,如果不允许为NULL,则值默认为允许值的第一个。

SET类型
SET是一个字符串对象，可以有零或者多个值，最多有64个值，在创建表时指定，用逗号隔开，具体语法如下：
SET('值1','值2','值3','值4'...'值5')
与ENUM一样，SET值再内部用整数表示，列表中每个值都有一个索引编号。与ENUM不同的是，ENUM再插入数据时，只能选择一个值插入，但是SET类型可以选择多个值插入。
如，创建表tmp
CREATE TABLE tmp(
s SET('a','b','c','d')
)
INSERT INTO tmp values('a'),('a,b,a'),('c,a,d');
INSERT INTO tmp values('a';'x');
那么第一句会插入成功，但是重复的值会被去掉，第二句会插入失败，因为x再定义时候不存在。

二进制字符串类型
MySQL中支持的二进制数据类型有：BIT,BINARY,VARBINARY,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB
BIT类型
BIT类型是位字段类型，M表示每个值得位数，范围是1-64，如果M被省略，则默认为1，如果为BIT位分配的值小于M位，则在值得左边用0填充，例如，BIT(6)存储一个‘101’，则会显示000101，最多是111111，大于这个值的数无法保存在数据库中。
创建表
CREATE TABLE tmp(
b BIT(4)
)
INSERT INTO tmp values(2),(9),(15)
SELECT BIN(b+0) FROM tmp
查询显示：10，1001，1111

BINARY和VARBINARY
BINARY和VARBINARY类型类似于CHAR和VARCHAR,不同的是，他们存储二进制字符串，使用时，语法如下：
列名称 BINARY(M) 或者VARBINARY(M)
BINARY是长度固定的，指定长度之后，不足最大长度的，在右边补充'\0',以达到指定长度。
VARBINARY存储的长度是可变的，指定好长度之后，其真实存储的内容的长度在0与最大值之间，，但其所占用的实际长度位M+1

BLOB
BLOB是一个二级制大对象，用来存储可变数量的数据，BLOB分为4种，TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB,
BLOB存储的是二进制字符串，没有字符集，TEXT存储的是非二进制字符串，有一个字符集，并且根据字符集进行排序和比较。

如何选择数据类型
MySQL提供了很多种数据类型，为了优化存储，提高数据库性能，在任何情况下均应使用最精确的类型。
整数和浮点数
如果不需要小数部分，则使用整数来保存数据，如果需要小数部分，则使用浮点类型，对于浮点类型数据，存入的数值会对该列定义的小数位进行4舍5入，例如，要存储1-99999，则用MEDIUMINT UNSIGNED,是最好的类型，如果要存储小数，则选择float类型。
浮点数包括单精度和双精度两种，如果要求的精度比较高，则选择DOUBLE类型。

日期与时间类型
MySQL对于不同种类的日期和时间有很多的数据类型，比如YEAR和TIME,如果只需要记录年份，则使用YEAR类型，如果只需要记录时间，则是要TIME类型
如果要记录日期和时间，则可以使用TIMESTAMP和DATETIME类型，因为DATETIME类型的存储范围比TIMESTAMP大，所以，要存储时间范围比较大的值时，用DATETIME.

浮点数和定点数
浮点数FLOAT，DOUBLE和定点数DECIMAL的优势是，在长度一定的情况下浮点数能表示更大的数据范围，但是由于浮点数有误差，因此对存储精度要求比较高时，要用
DECIMAL,

CHAR与VARCHAR
CHAR是固定长度的字符串，会自动删除插入数据的尾部的空格，VARHAR是边长的，不会删除尾部的空格。
CHAR是固定长度的，处理速度比VARCHAR要快，但是缺点就是浪费空间，所以，在不计较空间的情况下，可以选择CHAR,反之可以使用VARHCAR。
对于MyISM来讲，最好使用固定长度的数据列代替可变长度的数据列，这样可以使整个表静态化，从而使整个表检索更快，用空间换时间。
对于InnoDB来讲，使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际的长度存储，比较节省空间，所以对磁盘I/O和数据存储总量比较好

ENUM和SET
ENUM只能取单值，它的数据列表是一个枚举集合，它的合法取值列表最多有65535个成员，因此，在需要从多个值种选择一个值时，用ENUM,比如性别字段，每次只能取一个值。
SET
SET可以取多个值，它的合法取值列表最多有64个，在需要选择多个值得时候，可以选择SET类型，如用户注册时，存储用户得爱好。ENUM和SET的值是以字符串的形式出现的，但是在内部，MySQL以数字的形式来存储他们。

BLOB和TEXT
BLOB是二进制字符串，TEXT是非二进制字符串，两者均可以存储大量的数据，BLOB可以存储图片，视频等信息，而TEXT只能存储文本文件。

运算符
运算符是告诉MySQL进行特定运算的符号，主要有4大类，算术运算符，比较运算符，逻辑运算符，位操作符。

算术运算符
算术运算符用于各类数值运算，宝括加，减，乘，除，余。

比较运算符
比较运算符用于比较，包括大于，小于，等于，大于等于，小于等于，不等于。以及IN,BETWEEN AND ,IS NULL,GREATEST,LEAST,LIKE,REGEXP.

逻辑运算符
逻辑运算符的求值结果位1（true）或者0(false),这类运算符有NOT,AND ,OR ,XOR.

位操作符
位操作运算符将参与的操作数进行二进制位运算，宝括，按位与，按位或，按位非，异或，左移，右移。

安全等于运算符
整个操作符和=操作符执行相同的比较操作，不过<=>可以用来判断NULL值，在这两个操作数均为NULL时，其返回值位1而不位NULL,当其中一个操作数为NULL时，返回值为0而不为NULL.

LEAST运算符
语法格式为LEAST(值1，值2，值3，。。。。值n),返回最小值，假如任意一个值为NULL,则LEAST的返回值为NULL.

GREATEST运算符
语法格式为GREATEST(值1，值2，值3，。。。。值n),返回最大值，，假如任意一个值为NULL,则GREATEST的返回值为NULL.

REGEXP
REGEXP用来匹配字符串，语法格式为expr REGEXP,如果匹配格式则返回1，如果不匹配则返回0，常用的通配符有如下几种：
^,匹配以该字符后面的字符开头的字符串。
$,匹配以该字符前面的字符结尾的字符串。
.匹配任何一个单字符
[......]匹配在方括号种的任意字符，为了命名字符的范围，用一个“-”如，[a-z]匹配任何小写字母，[0-9]匹配数字。
*匹配0个或者多个在它前面的字符，比如x*匹配任何数量的*,[0-9]匹配任何数量的数字。*匹配任何数量的任何字符。

MySQL种如何使用特殊字符
如单引号',双引号“,使用时不能直接输入，而要用转义字符"\"

MySQL可以存储文件吗
MySQL种的BLOB和TEXT都可以存储很大的数据，可以用他们来存储比较图片，视频，大文本，但是这些字段会降低数据库的性能，如果并非比要，可以选择存储文件的路径。

MySQL中如何执行区分大小写的字符串比较
在WINDOWS平台下，MySQL是不分大小写的，如果想进行大小写的比较，可以在字符串前面加BINARY关键字。如默认情况下，a='A'返回1，如果使用BINARY关键字，
BINARY ‘a’='A',结果为0.

Tags：MySQL

很赞哦！ ()

上一篇：数据表的基本操作

下一篇：MySQL函数