MySQL数据类型

字面值类型(Literal value)

MySQL能够识别和使用的字面值类型包括:数值字符串日期/时间值十六进制值位字段值布尔值空值(NULL)

字符串字面值

MySQL中的字符串字面值可以使用单引号或者双引号表示,但推荐总是使用单引号

相邻的两个用单引号引用的字符串被当作一个字符串,比如:

SELECT 'a string'
SELECT 'a' ' ' 'string'

是一样的,但是要注意中间的空格字符串

如果启用ANSI_QUOTES模式,则双引号被当作一个标识符引用符

二进制字符串字面值是一个字节串,它不包含任何字符集和排序规则;非二进制字符串字面值是一个字符的序列,通常会有一个相关的字符集和排序规则。这两种字符串的对比都是基于各自的字符单元(byte character)。后者的排序规则由相应的collation来决定

字符串字面值可以使用一个可选的字符集引导标志和排序规则字句:

[_charset_name]'string' [COLLATE collation_name]

SELECT _latin1'string';
SELECT _latin1'string' COLLATE latin1_danish_ci;

You can use N’literal’ (or n’literal’) to create a string in the national character set. These statements are equivalent:

SELECT N'some text';
SELECT n'some text';
SELECT _utf8'some text';

如果未启用NO_BACKSLASH_ESCAPES模式,MySQL的字符串中也可以使用和编程语言类似的转义字符:\0 \' \" \b \n \r \t \\ \Z \% \_;转义字符后跟其他字符的话,转义字符被忽略

数值字面值

MySQL数值字面值包括精确值和近似值,Mysql对精确值进行精确计算,对近似值进行近似计算

精确值

整数 整数可以用十进制和十六进制来表示;十六进制值被默认当作字符串对待(每两个字符是一个ASCII字符),但是在进行数值运算的上下文里,十六进制被视为64位整数,运算结果也是64位整数

带小数点的有理数:由三个部分组成:一个数字序列、一个小数点再加一个数字序列,小数点前后的数字序列有一个可以为空;这种数值也叫做 DECIMAL NUMERIC FIXED

近似值

近似值类似于编程语言中的浮点数,带有一个底数和一个指数;具体的做法是在一个整数或者浮点数后面紧跟字母e或者E,然后是一个正负号,然后是一个以整数表示的指数

数值字面值可以加上一个正号或者负号来表示一个正数或者负数

在对表达式进行求值时MySQL根据以下规则进行运算:

  • 只要表达式里面有一个近似值就把其他的也当作近似值进行计算
  • 如果表达式里面全是整数形式的精确值,MySQL将使用BIGINT 64位精度对它进行求值
  • 如果表达式里面全是精确值,但其中有一个或者多个有小数部分,则使用65位精度进行DECIMAL运算(DECIMAL最大为65位)
  • 如果在求值过程中需要将某一个字符串转换为一个数值,则它会被转换为双精度浮点值。于是根据前面的规则求值的结果是浮点值
日期和时间字面值

日期和时间值可以用几种格式来表示,比如字符串或者数值。在MySQL期望接收一个日期类型值的上下文里'2015-07-21' '20150721' 20150721都被当作一个日期字面值

标准的SQL和ODBC日期时间字面值 标准SQL允许使用类型关键字和一个字符串构成一个日期时间字面值:

DATE 'str'
TIME 'str'
TIMESTAMP 'str'

空格是可选的,str要在特定的范围内;最后一种方式构建的日期类型是DATETIME

在日期和时间上下文中使用字符串和数值字面值

  • MySQL可以识别这些格式的DATE值:

    • 带分隔符的'YYYY-MM-DD' 'YY-MM-DD'格式的字符串,分隔符还可以使用/ ^ @
    • 不带分隔符的'YYYYMMDD' 'YYMMDD'格式的字符串
    • 不带分隔符的YYYYMMDD YYMMDD格式的数值
  • MySQL可以识别这些格式的DATETIME TIMESTAMP值:

    • 带分隔符的'YYYY-MM-DD HH:MM:SS' 'YY-MM-DD HH:MM:SS'格式的字符串,日期和时间中的分隔符都可以使用其他的,也可以混合使用;日期和时间之间可以使用空格也可以使用字符T
    • 不带分隔符的'YYYYMMDDHHMMSS' 'YYMMDDHHMMSS'格式的字符串
    • 不带分隔符的YYYYMMDDHHMMSS YYMMDDHHMMSS格式的数值

在未给出世纪元素的表示法中,MySQL按如下规则转换:70-99被转换1970-1999,00-69被转换为2000-2069

上面这些值必须在相应日期元素合法值的范围内,比如小时对应的值不能大于23

  • MySQL可以识别这些格式的TIME
    • 带分隔符的'D HH:MM:SS' 'HH:MM:SS' 'HH:MM' 'D HH:MM' 'D HH' 'SS'字符串值;D字符表示天可以是0-34之间的值
    • 不带分隔符的'HHMMSS'字符串值
    • 不带分隔符的HHMMSS数值

上面构成DATETIME TIMESTAMP TIME类型字面值的值后面还可以加一个小数点和六位数字来表示微秒;在使用分隔符的情况下如果表示日期时间元素的数值小于10,则单个的数字1 2 3表示01 02 03

十六进制字面值

MySQL支持十六进制的字面值,可以使用X'val' x'val'的形式(标准SQL语法)或者0xval的形式(ODBC语法),val是偶数的十六进制字符,如果val不是偶数则MySQL默认在前面加一个0

在数值上下文中,十六进制字面值被当作64为精度的数值;在字符串上下文中被当作一个二进制字符串,每对十六进制字符被转换成一个ASCII字符

默认情况下十六进制字面值被当作字符串。可以使用CAST(... AS UNSIGN)函数来进行转换

SELECT X'41', CAST(X'41' AS UNSIGNED);

要将一个字符串或者数值转换为十六进制字符串形式可以使用HEX()函数

SELECT HEX('cat');
布尔字面值

布尔字面值就是常量TRUEFALSE,在MySQL中他们同于1 0;字面值的大小写任意

位字段(Bit-Field)字面值

位字段字面值可以使用b'value' 0bvalue的格式表示,value0或者1构成的字符序列,这样的值本质上是二进制值

位字段字面值可以方便的用来给BIT数据列插入数据:

mysql> CREATE TABLE t (b BIT(8));
mysql> INSERT INTO t SET b = b'11111111';
mysql> INSERT INTO t SET b = b'1010';
mysql> INSERT INTO t SET b = b'0101';

位字段字面值表示的是二进制值,是不可打印的;如果要打印出这些值可以给它加上0或者使用BIN()函数。高位的0不打印

mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t;
+------+----------+----------+----------+
| b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
|  255 | 11111111 | 377      | FF       |
|   10 | 1010     | 12       | A        |
|    5 | 101      | 5        | 5        |
+------+----------+----------+----------+

为字段值赋给用户变量时被当作二进制字符串。要将位字段值作为数值可以使用CAST(... AS UNSIGN)函数或者给它加上0

空值

空值意味着没有数据。NULL可以任意大小写。NULL等同于\N

在使用LOAD DATA INFILE SELECT ... INTO OUTFILE语句导入或者导出的文件中NULL使用\N表示

存储类型

MySQL可存储5种大的数据类型: 数值类型 日期和时间类型 字符串类型 空间(spatial)类型 JSON数据类型;每种大的数据类型又分为很多种小的数据类型,下面进行详细描述

数值类型

BIT[(M)]
位字段类型,M表示bit的个数,可设置为1~64默认是1;M同时也指定了所占用的存储空间,如果实际存储的值小于M则系统会在左侧补0

在下面整型的描述中,修饰符 M 表示整型的最小显示宽度(如果宽度不够则进行右侧对齐),与相关类型可以存储的值的大小无关,最大可设置为255

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
非常小的整数,占1个字节,可以设置的值的范围是-128~127或者0~255

BOOL, BOOLEAN
等同于TINYINT(1),可以设置为0 false true和非零的TINYINT值;false等同于0,true等同于1,但是反过来0等同于false,非0值等同于true

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
小整数,占2个字节,可以设置的值的范围是-32768~32767或者0~65535

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
中等整数,占3个字节,可以设置的值的范围是-8388608 ~8388607或者0~16777215

INT/INTEGER[(M)] [UNSIGNED] [ZEROFILL]
整数,占4个字节,可以设置的值的范围是-2147483648~2147483647或者0~4294967295

BIGINT[(M)] [UNSIGNED] [ZEROFILL]
大整数,占8个字节,可以设置的值的范围是-9223372036854775808~9223372036854775808或者0~18446744073709551615

  • 可以将数值序列字符串插入到BIGINT数据列中
  • 所有的算术运算都是用BIGINT或者DOUBLE进行,如果参与计算的操作数过大则会产生错误的计算结果

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
精确小数,M表示可存储的数字的位数,D表示小数点后面的位数;M的最大值是65,D的最大值是30,小数点不计入;如果未指定D则为0表示没有小数部分,如果未指定M则默认值是10,也可以使用DEC、FIXED、NUMERIC

最大65位精度的小数可以是正或者负的值,UNSIGNED只是简单的不允许负的值插入,而不会像整型那样将可容纳的值的范围向正数方向平移

精确小数在内部使用二进制表示,存储时每九个数字占四个字节,并且整数部分和小数部分分开存储

FLOAT[(M[,D])] [UNSIGNED] [ZEROFILL]
单精度浮点数,占4个字节,可以设置值的范围是-3.402823466E+38~-1.175494351E-38 0或者1.175494351E-38~3.402823466E+38,实际的范围依赖你的硬件和操作系统;MD分别表示可存储的数字的位数和小数点后面的位数

DOUBLE[(M[,D])] [UNSIGNED] [ZEROFILL]
双精度浮点数,占8个字节,可以设置值的范围是-1.7976931348623157E+308~-2.2250738585072014E-308 0或者2.2250738585072014E-308~1.7976931348623157E+308,实际的范围依赖你的硬件和操作系统;MD分别表示可存储的数字的位数和小数点后面的位数

UNSIGNED用在浮点数列时,浮点数的可存储范围同样不会向正数方向平移

ZEROFILE修饰符表示如果列存储的实际值小于指定的显示宽度则在左侧补0,同时隐含UNSIGNED修饰符

SERIAL修饰符是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE修饰符的别名

SERIAL DEFAULT VALUE修饰符是NOT NULL AUTO_INCREMENT UNIQUE修饰符的别名

日期和时间类型

TIME DATETIME TIMESTAMP类型可以指定一个表示微秒精度的修饰符(fsp)fsp的取值范围是0-6,0和空都表示没有微秒部分

TIMESTAMP DATETIME可以设置自动初始化和更新属性

如果给一个列设置的值超过本列可表示的范围,则MySQL会将其设置为zero,但是TIME类型例外,超出TIME类型的列会被设置为TIME可以表示的最大或者最小值

几种类型的zero值检索结果如下:

DATE    '0000-00-00'
TIME    '00:00:00'
DATETIME    '0000-00-00 00:00:00'
TIMESTAMP   '0000-00-00 00:00:00'
YEAR    0000

在数值上下文中,MySQL会将日期和时间类型转换为数值进行处理

SUM() AVG()聚合函数不能直接使用日期时间类型的列,但是我们可以使用一种变通的方法:先将日期时间值转换为数值单位,进行聚合操作,然后再转换回来,比如

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

DATE
日期,支持的值的范围是1000-01-019999-12-31。MySQL显示日期类型值为YYYY-MM-DD的格式,但是可以使用很多种日期字面值来给他赋值

DATETIME[(fsp)]
日期和时间组合值,支持的值的范围是1000-01-01 00:00:00.0000009999-12-31 23:59:59:999999。MySQL显示日期时间类型值为YYYY-MM-DD HH:MM:SS[.franction]的格式,但是可以使用很多种日期时间字面值来给他赋值

设置DEFAULTON TIME列修饰符可以让DATETIME类型列值自动初始化和更新为当前的时间

如果未启用NO_ZERO_DATE模式,上面两种类型中的月和日可以设置为00

TIMESTAMP[(fsp)]
时间戳,支持的值范围是UTC时间1970-01-01 00:00:01.0000002038-01-19 03:14:07.999999,TIMESTAMP被存储为自epoch(1970-01-01 00:00:00 UTC)以来的秒数;MySQL中的时间戳不能表示1970-01-01 00:00:01是因为它会被存储为0,而0要用来表示0000-00-00 00:00:00这个TIMESTAMP值

如果未设置explicit_defaults_for_timestamp系统变量(默认OFF),表中的第一个TIMESTAMP列自动有设置为初始化时间或者最近一次更新时间的属性,也可以使用DEFAULT CURRENT_TIMESTAMPON TIME CURRENT_TIMESTAMP列修饰符显式设置

如果一个TIMESTAMP列有设置为初始化时间或者最近一次更新时间的属性,并且在插入值时未指定值或者指定NULL值(这一列有NULL修饰符例外),则这列被更新为当前时间

TIME[(fsp)]
时间,范围是-838:59:59.000000838:59:59.000000,MySQL显示时间类型值为HH:MM:SS[.franction]的格式,但是可以使用很多种时间字面值来给他赋值

YEAR[(4)]
4位数字的年份值,MySQL显示年份类型值为YYYY的格式,但是可以使用很多种年份字面值来给他赋值。值可以显示为1901到2155 和 0000

字符串类型

CHAR VARCHAR TEXT类型后面的M的单位是character

CHAR VARCHAR TEXT ENUM SET类型后面可以指定字符集和排序规则,如果未指定字符集则使用系统默认字符集

如果指定字符集为binary则导致字符类型转变为相应的二进制数据类型:CHAR转变为BINARY VARCHAR转变为VARBINARY TEXT转变为BLOB;而ENUM SET不转换

CHAR VARCHAR TEXT类型可以使用BINARY修饰符,这表示本行的排序规则是默认字符集的二进制排序规则

不带M长度修饰符的列类型,表示MySQL一定会分配可以容纳本列允许最大字符/字节数的存储空间,如果受制于行最大字节数的限制,则会报错

CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
定长字符串,M可取值的范围是0-255默认是1,如果插入的值小于给定的长度则在右侧补空格,如果未启用PAD_CHAR_TO_FULL_LENGTH模式,则取出数据时会去掉末尾的空格

允许定义CHAR(0)这样的列,如果再使用NULL修饰符则这一列只能有两个值NULL ''

VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
变长字符串,M指定了列可存储最大的字符数,范围是0-65535。实际的最大值受制于行最大字节数–65535bytes,而且是行中所有列的和,并且和使用的字符集有关。所以如果使用utf8字符集则最大可以指定的M值为21844

MySQL会在VARCHAR列的数据前加上一个或者两个字节来表示列长度,如果数据长度小于255 bytes则使用一个字节,否则使用两个字节

BINARY(M)
类似于CHAR类型,但是这里存储的是二进制字节串

VARBINARY(M)
类似于VARCHAR类型,但是这里存储的是二进制字节串

TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
最大长度为255字符的文本列。有效最大长度会因为使用多字节字符集类型而减少。使用1个字节的前缀表示内容长度的字节数

TEXT(M) [CHARACTER SET charset_name] [COLLATE collation_name]
最大长度为65535字符的文本列。有效最大长度会因为使用多字节字符集类型而减少。使用2个字节的前缀表示内容长度的字节数;M表示列允许的最大字符数

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
最大长度为16,777,215字符的文本列。有效最大长度会因为使用多字节字符集类型而减少。使用3个字节的前缀表示内容长度的字节数

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
最大长度为4,294,967,295字符的文本列。有效最大长度会因为使用多字节字符集类型而减少,同时还受制于配置的客户端/服务器最大包缓冲池大小和系统内存大小。使用4个字节的前缀表示内容长度的字节数

TINYBLOG
类似于TINYTEXT,但这里存储的是二进制数据

BLOG(M)
类似于BLOG(M),但这里存储的是二进制数据

MEDIUMBLOG
类似于MEDIUMTEXT,但这里存储的是二进制数据

LONGBLOG(M)
类似于LONGTEXT,但这里存储的是二进制数据

BLOBTEXT类型可以看作是VARBINARYVARCHAR类型,但是有两点不同:前者只能创建前缀索引,而后者前缀索引是可选的;前者不能有DEFAULT

BLOBTEXT类型在存储时单独分配存储对象

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
枚举类型,一个字符串对象,并且只能是后面value指定的值其中的一个或者是NULL;ENUM类型在内部使用整型表示

SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
集合类型,可以是一个或者多个后面value列表中指定的值;SET类型在内部使用整型表示

坚持原创技术分享,您的支持将鼓励我继续创作!