武昌做网站哪家好,家具设计图片,电子政务网站课程设计,《网站设计与建设》电子书文章目录 前言概述数字类型数字类型语法数字类型字面量十六进制字面量位字面量布尔字面量 数字类型的属性超出范围和溢出处理 时间和日期类型时间和日期类型语法DATE、DATETIME和TIMESTAMP的异同TIMESTAMP和DATETIME的自动初始化和更新时间和日期字面量 字符串类型字符串类型语… 文章目录 前言概述数字类型数字类型语法数字类型字面量十六进制字面量位字面量布尔字面量 数字类型的属性超出范围和溢出处理 时间和日期类型时间和日期类型语法DATE、DATETIME和TIMESTAMP的异同TIMESTAMP和DATETIME的自动初始化和更新时间和日期字面量 字符串类型字符串类型语法CHAR和VARCHAR的异同BINARY和VARBINARY的异同BLOB和TEXT的异同ENUMSET字符串字面量 JSON数据类型空间数据类型数据类型的默认值MySQL 8.0.13的显式默认值处理MySQL 8.0.13之前的显式默认处理隐式默认值处理 NULL 前言
本文翻译自官方文档。
概述
MySQL支持以下几种SQL数据类型
数字类型日期和时间类型字符串字符和字节类型空间类型JSON数据类型
本章提供了对每个类别中类型的属性的概述和更详细的描述数据类型描述使用以下约定
对于整数类型M表示最大显示宽度。对于浮点型和定点型M是可以存储的总位数。对于字符串类型M是最大长度。M的最大允许值取决于数据类型。D适用于浮点和定点类型表示小数点后的位数。最大值为30但不大于M−2。fsp适用于TIME、DATETIME和TIMESTAMP类型表示小数秒精度即秒的小数部分的小数点后面的位数。如果给定fsp值则必须在0到6的范围内。值为0表示没有小数部分。如果省略默认精度为0。方括号表示类型定义的可选部分。
数字类型
MySQL支持所有标准的SQL数字数据类型。这些类型包括精确数字数据类型INTEGER、SMALLINT、DECIMAL和NUMERIC以及近似数字数据类型FLOAT、REAL和DOUBLE PRECISION。关键字INT是INTEGER的同义词关键字DEC和FIXED是DECIMAL的同义词。MySQL将DOUBLE视为DOUBLE PRECISION一个非标准扩展的同义词。MySQL也将REAL视为DOUBLE PRECISION的同义词除非启用了REAL_AS_FLOAT SQL模式。BIT数据类型存储位值支持MyISAM、MEMORY、InnoDB和NDB表。
数字类型语法
对于整数数据类型M表示最小显示宽度。最大显示宽度为255。显示宽度与类型可以存储的值范围无关。对于浮点和定点数据类型M是可以存储的总位数。整数数据类型允许UNSIGNED属性和SIGNED属性其中SIGNED属性是默认的。
BIT[(M)]用于存储位值。M表示每个值的位数取值范围为1 ~ 64。如果省略M则默认为1。TINYINT[(M)] [UNSIGNED]一个非常小的整数。取值范围为-128 ~ 127。无符号范围是0到255。BOOL, BOOLEAN这些类型是TINYINT(1)的同义词。值为零被认为是false。非零值被认为是true
mysql SELECT IF(0, true, false);
------------------------
| IF(0, true, false) |
------------------------
| false |
------------------------mysql SELECT IF(1, true, false);
------------------------
| IF(1, true, false) |
------------------------
| true |
------------------------mysql SELECT IF(2, true, false);
------------------------
| IF(2, true, false) |
------------------------
| true |
------------------------然而值TRUE和FALSE分别只是1和0的别名如下所示
mysql SELECT IF(0 FALSE, true, false);
--------------------------------
| IF(0 FALSE, true, false) |
--------------------------------
| true |
--------------------------------mysql SELECT IF(1 TRUE, true, false);
-------------------------------
| IF(1 TRUE, true, false) |
-------------------------------
| true |
-------------------------------mysql SELECT IF(2 TRUE, true, false);
-------------------------------
| IF(2 TRUE, true, false) |
-------------------------------
| false |
-------------------------------mysql SELECT IF(2 FALSE, true, false);
--------------------------------
| IF(2 FALSE, true, false) |
--------------------------------
| false |
--------------------------------SMALLINT[(M)] [UNSIGNED]一个小整数。带符号的范围是-32768到32767。无符号取值范围是0 ~ 65535。MEDIUMINT[(M)] [UNSIGNED]中等大小的整数。带符号的范围是-8388608 ~ 8388607。无符号范围是0到16777215。INT[(M)] [UNSIGNED]正常大小的整数。带符号的范围是-2147483648 ~ 2147483647。无符号范围是0 ~ 4294967295。BIGINT[(M)] [UNSIGNED]大整数。符号范围为-9223372036854775808 ~ 9223372036854775807。无符号取值范围是0 ~ 18446744073709551615。DECIMAL[(M[,D])]一个紧凑的精确定点数字。FLOAT单精度浮点数MySQL为单精度值使用4个字节取值范围为-3.402823466E38 ~ - 1.175494351e - 38、0和1.175494351E-38 ~ 3.402823466E38。DOUBLE双精度浮点数MySQL为双精度值使用8个字节。取值范围为-1.7976931348623157E308 ~ - 2.2250738585072014e - 308,0和2.2250738585072014E-308 ~ 1.7976931348623157E308。
MySQL中所有算术都是使用有符号的BIGINT或DOUBLE值完成。
数字类型字面量
数字字面值包括精确值整数和DECIMAL字面量和近似值浮点字面量。
整数用数字序列表示。数字可能包括.作为小数分隔符。数字前面可以加-或分别表示负值或正值。用尾数和指数表示的科学记数法中的数字是近似值数字。精确值数字字面值有整数部分或小数部分或两者兼而有之。近似值数值在科学记数法中用尾数和指数表示。
两个看起来相似的数字可能会被区别对待。例如2.34是一个精确值而2.34E0是一个近似值。
十六进制字面量
十六进制文字值使用Xval表示法写入其中val包含十六进制数字。数字和任何前导X的字母大小写不重要。
X01AF
X01af
x01AF
x01af使用Xval表示法写入的值必须包含偶数位否则会出现语法错误。要纠正这个问题在值中填充一个前导零。
默认情况下十六进制字面值是一个二进制字符串其中每对十六进制数字代表一个字符。
mysql SELECT X4D7953514C, CHARSET(X4D7953514C);
---------------------------------------
| X4D7953514C | CHARSET(X4D7953514C) |
---------------------------------------
| MySQL | binary |
---------------------------------------十六进制字面值可以有一个可选的字符集引入器和COLLATE子句将其指定为使用特定字符集和排序规则的字符串
[_charset_name] Xval [COLLATE collation_name]在数字上下文中MySQL将十六进制字面值视为BIGINT UNSIGNED64位无符号整数。要确保十六进制字面值的数字处理请在数字上下文中使用它。实现此目的的方法包括添加0或使用CAST(… AS UNSIGNED)。
mysql SET v1 X41;
mysql SET v2 X410;
mysql SET v3 CAST(X41 AS UNSIGNED);
mysql SELECT v1, v2, v3;
------------------
| v1 | v2 | v3 |
------------------
| A | 65 | 65 |
------------------空的十六进制值X 计算为零长度的二进制字符串。转换成数字后结果为0
mysql SELECT CHARSET(X), LENGTH(X);
---------------------------
| CHARSET(X) | LENGTH(X) |
---------------------------
| binary | 0 |
---------------------------
mysql SELECT X0;
-------
| X0 |
-------
| 0 |
-------位字面量
位值文字使用bval表示法书写。Val是一个用0和1写成的二进制值。以字母b开头的字母不重要。
b01
B01默认情况下位值字面值是一个二进制字符串
mysql SELECT b1000001, CHARSET(b1000001);
---------------------------------
| b1000001 | CHARSET(b1000001) |
---------------------------------
| A | binary |
---------------------------------位值字面值可以有一个可选的字符集引入器和COLLATE子句将其指定为使用特定字符集和排序规则的字符串
[_charset_name] bval [COLLATE collation_name]在数字上下文中MySQL将位字面值视为整数。要确保位字面值的数字处理请在数字上下文中使用它。实现此目的的方法包括添加0或使用CAST(…unsigned)。
mysql SET v1 b1100001;
mysql SET v2 b11000010;
mysql SET v3 CAST(b1100001 AS UNSIGNED);
mysql SELECT v1, v2, v3;
------------------
| v1 | v2 | v3 |
------------------
| a | 97 | 97 |
------------------对于位字面值位操作被认为是数字上下文但在MySQL 8.0及更高版本中位操作允许数字或二进制字符串参数。要显式地为位字面值指定二进制字符串上下文请为至少一个参数使用_binary引入器
mysql SET v1 b000010101 | b000101010;
mysql SET v2 _binary b000010101 | _binary b000101010;
mysql SELECT HEX(v1), HEX(v2);
--------------------
| HEX(v1) | HEX(v2) |
--------------------
| 3F | 003F |
--------------------对于这两个位操作显示的结果看起来很相似但是没有_binary的结果是一个BIGINT值而有_binary的结果是一个二进制字符串。由于结果类型不同因此显示的值不同:数值结果不显示高阶0。
布尔字面量
常量TRUE和FALSE的值分别为1和0。常数名可以用任何字母形式书写。
mysql SELECT TRUE, true, FALSE, false;- 1, 1, 0, 0数字类型的属性
MySQL支持一个扩展可以在类型的基本关键字后面的括号中选择性地指定整数数据类型的显示宽度。例如INT(4)指定一个显示宽度为四位数的INT。
显示宽度不约束可存储在列中的值的范围。它也不会阻止比列显示宽度宽的值被正确显示。例如指定为SMALLINT(3)的列具有通常的SMALLINT范围-32768到32767并且三位数允许的范围之外的值将使用三个以上的数字完整显示。
所有的整数类型都可以有一个可选的UNSIGNED属性。无符号类型可用于仅允许在列中使用非负数或者当需要为列设置更大的上数值范围时。例如如果一个INT列是UNSIGNED列的范围的大小是相同的但是它的端点向上移动从-2147483648和2147483647到0和4294967295。
整数类型可以具有AUTO_INCREMENT属性。在AUTO_INCREMENT列中插入NULL值时该列将被设置为下一个序列值。通常是value1其中value是表中当前列的最大值。(AUTO_INCREMENT序列以1开头。)
将0存储到AUTO_INCREMENT列中与存储NULL具有相同的效果除非启用了NO_AUTO_VALUE_ON_ZERO SQL模式。
插入NULL来生成AUTO_INCREMENT值需要将列声明为NOT NULL。如果列声明为NULL则插入NULL存储NULL。当您在AUTO_INCREMENT列中插入任何其他值时该列将被设置为该值并重置序列以便下一个自动生成的值按照插入值的顺序出现。
AUTO_INCREMENT列不支持负值。
CHECK约束不能引用具有AUTO_INCREMENT属性的列也不能将AUTO_INCREMENT属性添加到CHECK约束中使用的现有列中。
超出范围和溢出处理
当MySQL在列数据类型允许范围之外的数值列中存储值时结果取决于当时有效的SQL模式
如果启用严格SQL模式根据SQL标准MySQL会拒绝超出范围的值并给出错误插入失败。如果没有启用限制模式MySQL将值剪辑到列数据类型范围的适当端点并存储结果值。
时间和日期类型
表示时间值的日期和时间数据类型是DATE、TIME、DATETIME、TIMESTAMP和YEAR。每个时间类型都有一个有效值范围以及一个零值当你指定一个MySQL无法表示的无效值时可能会使用这个零值代替。MySQL允许您存储一个零值作为虚拟日期。在某些情况下这比使用NULL值更方便并且使用更少的数据和索引空间。要禁用零值请启用NO_ZERO_DATE模式。下表显示了每种类型的零值的格式。零值是特殊的但是您可以使用表中显示的值显式地存储或引用它们。您也可以使用值0或0来完成此操作。
类型零值DATE‘0000-00-00’TIME‘00:00:00’DATETIME‘0000-00-00 00:00:00’TIMESTAMP‘0000-00-00 00:00:00’TEAR0000
时间和日期类型语法
MySQL允许为TIME、DATETIME和TIMESTAMP值设置小数秒精度可达微秒。要定义包含小数秒部分的列请使用语法type_name(fsp)其中type_name是类型名fsp是小数秒精度。例如
CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));如果给定fsp值则必须在0到6的范围内。值为0表示没有小数部分。如果省略默认精度为0。
DATEDATETIME[(fsp)]TIMESTAMP[(fsp)]TIME[(fsp)]YEAR
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、DATETIME和TIMESTAMP的异同
DATE、DATETIME和TIMESTAMP类型是相关的。本节描述它们的特点、相似之处和不同之处。
DATE类型用于有日期部分但没有时间部分的值。 MySQL以YYYY-MM-DD格式检索和显示DATE值。支持的范围是10001-01-01 ~ 99999-12-31。 DATETIME类型用于同时包含日期和时间部分的值。 MySQL检索并显示YYYY-MM-DD hh:mm:ss格式的DATETIME值。支持的范围为:10001-01 00:00:00 ~ 99999-12-31 23:59:59。 TIMESTAMP数据类型用于同时包含日期和时间部分的值。 TIMESTAMP的取值范围是’1970-01-01 00:00:01’ UTC到’ 20388-01-19 03:14:07’ UTC。MySQL将TIMESTAMP值从当前时区转换为UTC进行存储并将UTC转换回当前时区进行检索。DATETIME等其他类型不会发生这种情况。默认情况下每个连接的当前时区是服务器的时间。时区可以基于每个连接进行设置。只要时区设置保持不变就会得到与存储相同的值。如果你存储了一个TIMESTAMP值然后修改了时区并取得了这个值那么取得的值与你存储的值是不同的。发生这种情况是因为在两个方向的转换中没有使用相同的时区。当前时区可以通过系统变量time_zone获取。 DATETIME或TIMESTAMP值可以包含尾部小数秒部分精度可达微秒。特别是插入到DATETIME或TIMESTAMP列中的值中的任何小数部分都会被存储而不是丢弃。如果包含小数部分则这些值的格式为YYYY-MM-DD hh:mm:ss[.fraction] DATETIME值的范围为’ 10000-01-01 00:00:00.000000’到’ 99999-12-31 23:59:59.499999’TIMESTAMP值的范围为’1970-01-01 00:00:01.000000’到’ 20388-01-19 03:14:07.499999’。TIMESTAMP和DATETIME数据类型支持对当前日期和时间的自动初始化和更新。如果SQL模式允许转换无效的DATE、DATETIME或TIMESTAMP值将被转换为相应类型的零值。
TIMESTAMP和DATETIME的自动初始化和更新
使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句可以将TIMESTAMP和DATETIME列设置为自动初始化并更新为当前日期和时间。自动初始化和更新的时机如下
对于未为该列指定值的插入语句将自动初始化列设置为当前时间戳。当插入语句中任何其他列的值从当前值更改时自动更新列将自动更新为当前时间戳。
如果禁用了explicit_defaults_for_timestamp系统变量
第一个TIMESTAMP列将同时具有DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP。TIMESTAMP列默认NOT NULL不能包含NULL值指定NULL值则指定当前时间戳。要允许时间戳列包含NULL需要显式地使用NULL属性声明它。在这种情况下默认值也会变成NULL。此时赋值NULL会将其设置为NULL而不是当前时间戳。
如果启用了explicit_defaults_for_timestamp系统变量
那么TIMESTAMP列只有在使用NULL属性声明时才允许NULL值。TIMESTAMP列不允许被赋值为NULL无论是用NULL还是NOT NULL属性声明。
时间和日期字面量
日期和时间值可以用几种格式表示例如引号字符串或数字具体取决于值的确切类型和其他因素。例如在MySQL需要日期的上下文中它将2015-07-2120150721和20150721中的任何一个解释为日期。
标准SQL要求使用类型关键字和字符串指定时间和日期字面量。关键字和字符串之间的空格是可选的。
DATE str
TIME str
TIMESTAMP strMySQL可以识别但与标准SQL不同它不需要类型关键字。要与标准兼容的应用程序应该包括用于时间和日期字面量的类型关键字。
MySQL可以识别以下格式的DATE值
作为YYYY-MM-DD或YY-MM-DD格式的字符串。作为YYYYMMDD或YYMMDD格式的无分隔符的字符串前提是该字符串作为日期有意义。如果没有意义则会变成0000-00-00。作为YYYYMMDD或YYMMDD格式的数字前提是该数字作为日期有意义。
MySQL可以识别以下格式的DATETIME和TIMESTAMP值
格式为YYYY-MM-DD hh:mm:ss或YY-MM-DD hh:mm:ss的字符串。作为YYYYMMDDhhmmss或YYMMDDhhmmss格式的没有分隔符的字符串前提是该字符串作为日期有意义没有意义的会变成0000-00-00 00:00:00。作为YYYYMMDDhhmmss或YYMMDDhhmmss格式的数字前提是该数字作为日期有意义。
DATETIME或TIMESTAMP值可以包含尾部小数秒部分精度可达微秒。小数部分与其余部分之间总是要隔一个小数点。
包含两位数年份值的日期是不明确的因为世纪未知。MySQL使用以下规则解释两位数的年份值
70-99范围内的年份值变为1970-1999。00-69范围内的年份变为2000-2069。
对于指定为包含日期部分的字符串的值不需要为小于10的月或日值指定两位数字。 2015-06-9 与’2015-06-09相同。类似地对于指定为包含时间部分的字符串的值不需要为小于10的小时、分钟或秒值指定两位数字。2015-10-30 1:2:3与2015-10-30 01:02:03相同。
指定为数字的值长度应为6、8、12或14位。如果数字长度为8位或14位则假定其格式为YYYYMMDD或YYYYMMDDhhmmss并且年份由前4位数字给出。如果数字长度为6或12位则假定其格式为YYMMDD或YYMMDDhhmmss并且年份由前2位数字给出。不属于这些长度的数字将被解释为在最接近的长度前填充前导零。
指定为非分隔符字符串的值根据其长度进行解释。对于长度为8或14个字符的字符串年份被假定为前4个字符。否则年份由前两个字符表示。这个字符串从左到右解析以查找年、月、日、小时、分钟和秒的值与字符串中包含的部分相同。这意味着不应该使用少于6个字符的字符串。例如如果你指定9903认为它代表1999年3月MySQL会将其转换为“0”日期值。这是因为年和月的值分别为99和03而日部分则完全缺失。但是您可以显式指定0值来表示缺少的月份或日期部分。例如要插入值1999-03-00使用990300。
MySQL可以识别以下格式的TIME值
作为D hh:mm:ss格式的字符串作为hhmmss格式的没有分隔符的字符串只要它作为时间有意义无意义的会变成00:00:00。作为hhmmss格式的数字前提是它作为时间有意义。
在D hh:mm:ss.fraction、hh:mm:ss.fraction、hh:mm:ss.fraction和hhmms .fraction时间格式中可以识别后面的小数部分其中fraction是小数部分精度最高为微秒。小数部分与其余部分之间总是要隔一个小数点。
对于指定为包含时间部分分隔符的字符串的TIME值不需要为小于10的小时、分钟或秒值指定两个数字。8:3:2和08:03:02是一样的。
字符串类型
字符串数据类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
字符串类型语法
对于字符串列CHAR、VARCHAR、TEXT、ENUM和SET类型的定义MySQL以字符单位解释长度规范。对于二进制字符串列BINARY、 VARBINARY和BLOB类型的定义MySQL以字节单位解释长度规范。
字符串数据类型的列定义可以指定列字符集和排序规则
CHARACTER SET指定字符集。如果需要可以使用COLLATE属性以及任何其他属性指定字符集的排序规则。
CREATE TABLE t
(c1 VARCHAR(20) CHARACTER SET utf8mb4,c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);为字符串数据类型指定CHARACTER SET binary属性将导致将该列创建为相应的二进制字符串数据类型CHAR变为BINARYVARCHAR变为VARBINARYTEXT变为BLOB。对于ENUM和SET数据类型这种情况不会发生它们是按照声明创建的。
字符串数据类型的列的比较和排序都是基于分配给列的排序规则进行的。
CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] 一个固定长度的字符串在存储时总是用空格右填充到指定的长度。M表示以字符为单位的列长度。取值范围为0 ~ 255。如果省略M则长度为1。MySQL允许创建CHAR(0)类型的列。这主要是在您必须与依赖于列的存在但实际上不使用其值的旧应用程序兼容时有用的。当您需要一个只能接受两个值的列时CHAR(0)也非常好因为定义为CHAR(0) NULL的列只占用一个位并且只能接受值NULL和空字符串。 VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name] 可变长度的字符串。M表示以字符为单位的最大列长度。M的取值范围是0 ~ 65535。VARCHAR的有效最大长度取决于最大行大小和所使用的字符集。例如utf8mb3字符每个字符最多需要三个字节因此可以将使用utf8mb3字符集的VARCHAR列声明为最多21,844个字符。MySQL将VARCHAR值存储为1字节或2字节长度的前缀加上数据。长度前缀表示值中包含的字节数。如果值需要不超过255个字节则VARCHAR列使用一个长度字节如果值需要超过255个字节则使用两个长度字节。 BINARY[(M)] BINARY类型类似于CHAR类型但存储的是二进制字节字符串而不是非二进制字符串。可选的长度M表示以字节为单位的列长度。如果省略M默认为1。VARBINARY(M)VARBINARY类型类似于VARCHAR类型但存储的是二进制字节字符串而不是非二进制字符串。M表示以字节为单位的最大列长度。TINYBLOBBLOB列最大长度为255字节。每个TINYBLOB值都使用一个1字节长度的前缀来存储该前缀表示值中的字节数。TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]TEXT列最大长度为255个字符。如果值包含多字节字符则有效最大长度较小。每个TINYTEXT值都使用一个1字节长度的前缀来存储该前缀表示值中的字节数。BLOB[(M)] BLOB列最大长度为65,535字节。每个BLOB值都使用一个2字节长度的前缀来存储该前缀表示值中的字节数。对于这种类型可以给出一个可选的长度M。如果这样做了MySQL将该列创建为最小的BLOB类型其大小足以容纳M字节长的值。 TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] TEXT列最大长度为65,535个字符。如果值包含多字节字符则有效最大长度较小。每个TEXT值都使用一个2字节长度的前缀来存储该前缀表示值中的字节数对于这种类型可以给出一个可选的长度M。如果这样做了MySQL将该列创建为最小的TEXT类型其大小足以容纳M个字符长的值。 MEDIUMBLOBBLOB列最大长度为16,777,215字节。每个MEDIUMBLOB值都使用一个3字节长度的前缀来存储该前缀表示该值的字节数。MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]TEXT列最大长度为16,777,215个字符。如果值包含多字节字符则有效最大长度较小。每个MEDIUMTEXT值都使用一个3字节长度的前缀来存储该前缀表示该值中的字节数。LONGBLOBBLOB列最大长度为4GB字节。LONGBLOB列的有效最大长度取决于客户机/服务器协议中配置的最大数据包大小和可用内存。每个LONGBLOB值都使用一个4字节长度的前缀来存储该前缀表示值中的字节数。LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]TEXT列最大长度为4GB个字符。如果值包含多字节字符则有效最大长度较小。LONGTEXT列的有效最大长度还取决于客户机/服务器协议中配置的最大数据包大小和可用内存。每个LONGTEXT值都使用一个4字节长度的前缀来存储该前缀表示该值中的字节数。ENUM(value1,value2,...) [CHARACTER SET charset_name] [COLLATE collation_name] 枚举。一个字符串对象只能有一个值从值列表value1 value2…、NULL、特殊的错误值中选择。ENUM值在内部表示为整数。一个ENUM列最多可以有65,535个不同的元素。单个ENUM元素支持的最大长度是 M 255 M 255 M255和 ( M × w ) 1020 (M\times w) 1020 (M×w)1020其中M是元素文字长度w是字符集中最大长度字符所需的字节数。 SET(value1,value2,...) [CHARACTER SET charset_name] [COLLATE collation_name] 一个集合。可以有零个或多个值的字符串对象每个值必须从值列表value1 value2…中选择。SET值在内部表示为整数。一个SET列最多可以有64个不同的成员。单个SET元素支持的最大长度是 M 255 M 255 M255和 ( M × w ) 1020 (M\times w) 1020 (M×w)1020其中M是元素文字长度w是字符集中最大长度字符所需的字节数。
CHAR和VARCHAR的异同
CHAR和VARCHAR类型相似但存储和检索的方式不同。它们在最大长度和是否保留尾随空格方面也有所不同。
CHAR和VARCHAR类型声明了一个长度该长度指示您希望存储的最大字符数。例如CHAR(30)最多可以容纳30个字符。CHAR列的长度固定为创建表时声明的长度。长度为0 ~ 255之间的任意值。当存储CHAR值时它们用空格右填充到指定的长度。当检索CHAR值时除非启用了PAD_CHAR_TO_FULL_LENGTH SQL模式否则将删除尾随空格。VARCHAR列中的值是可变长度的字符串。长度范围为0 ~ 65535。VARCHAR的有效最大长度取决于最大行大小和所使用的字符集。与CHAR相反VARCHAR值存储为1字节或2字节长度的前缀加上数据。长度前缀表示值中包含的字节数。如果值需要不超过255个字节则列使用一个长度字节如果值需要超过255个字节则使用两个长度字节。
下表通过显示将各种字符串值存储到CHAR(4)和VARCHAR(4)列中的结果来说明CHAR和VARCHAR之间的区别假设该列使用单字节字符集。 表最后一行显示的值仅在不使用严格SQL模式时才适用;如果启用严格模式则不会存储超过列长度的值并导致错误。
InnoDB将大于或等于768字节的固定长度字段编码为可变长度字段可以存储在页外。
BINARY和VARBINARY的异同
BINARY和VARBINARY类型类似于CHAR和VARCHAR除了它们存储二进制字符串而不是非二进制字符串。也就是说它们存储字节字符串而不是字符串。这意味着它们具有二进制字符集和排序比较和排序基于值中字节的数值。
BINARY和VARBINARY允许的最大长度与CHAR和VARCHAR相同。
如果没有启用严格的SQL模式并且您为BINARY或VARBINARY列分配的值超过了列的最大长度则该值将被截断以适合该列并生成警告。
当BINARY值被存储时它们被0x00零字节右填充到指定的长度并且在检索时不删除尾随字节。零字节和空格在比较时不同零字节排在空格前边。
对于VARBINARY插入时没有填充检索时也不会剥离字节。
对于那些删除尾垫字节或比较忽略它们的情况如果列具有需要唯一值的索引则在列中插入仅在尾垫字节数上不同的值将导致重复键错误。例如如果一个表包含a尝试存储a\0会导致重复键错误。
如果计划使用BINARY数据类型存储二进制数据并且要求检索的值与存储的值完全相同则应该仔细考虑前面的填充和剥离特征。下面的例子说明了零字节填充如何影响列值比较
mysql CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.01 sec)mysql INSERT INTO t SET c a;
Query OK, 1 row affected (0.01 sec)mysql SELECT HEX(c), c a, c a\0\0 from t;
------------------------------
| HEX(c) | c a | c a\0\0 |
------------------------------
| 610000 | 0 | 1 |
------------------------------
1 row in set (0.09 sec)如果检索到的值必须与没有填充的存储指定的值相同那么最好使用VARBINARY或BLOB数据类型之一。
BLOB和TEXT的异同
BLOB是一种二进制大对象可以保存可变数量的数据。这四种BLOB类型是TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们的不同之处在于它们所能保存的值的最大长度。四种TEXT类型分别是TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。它们对应于四种BLOB类型并且具有相同的最大长度和存储需求。
BLOB值被视为二进制字符串。它们具有二进制字符集和排序比较和排序基于列值中字节的数值。TEXT值被视为非二进制字符串。它们具有二进制以外的字符集并且根据字符集的排序对值进行排序和比较。
如果没有启用严格的SQL模式并且您为BLOB或TEXT列分配的值超过了列的最大长度则该值将被截断以适合该列并生成警告。无论使用何种SQL模式截断要插入TEXT列的值的多余尾随空格总是会生成警告。
对于TEXT和BLOB列在插入时没有填充在选择时也不会剥离字节。如果对TEXT列进行了索引则索引条目比较将在末尾填充空格。这意味着如果索引需要唯一的值则只在末尾空格的数量上不同的值会发生重复键错误。例如如果一个表包含a尝试存储a 会导致重复键错误。
在大多数情况下可以将BLOB列视为VARBINARY列其大小可以随您的喜好而定。类似地您可以将TEXT列视为VARCHAR列。BLOB和TEXT与VARBINARY和VARCHAR的区别如下
对于BLOB列和TEXT列上的索引必须指定索引前缀长度。对于CHAR和VARCHAR前缀长度是可选的。BLOB和TEXT列不能有DEFAULT值。
因为BLOB和TEXT值可能非常长所以在使用它们时可能会遇到一些限制
排序时只使用列的第一个max_sort_length字节。max_sort_length的默认值是1024。通过在服务器启动或运行时增加max_sort_length的值可以使排序或分组中有更多重要的字节。使用临时表处理的查询结果中的BLOB或TEXT列实例会导致服务器使用磁盘上的表而不是内存中的表因为内存存储引擎不支持这些数据类型。使用磁盘会导致性能损失因此只有在确实需要时才在查询结果中包含BLOB或TEXT列。例如避免使用SELECT *因为它选择所有列。
每个BLOB或TEXT值在内部由一个单独分配的对象表示。这与所有其他数据类型不同其他数据类型在打开表时为每列分配一次存储。
ENUM
ENUM是一个字符串对象其值从允许的值列表中选择这些值在表创建时在列规范中显式枚举。ENUM类型有以下优点
在列可能值的集合有限的情况下压缩数据存储。指定作为输入值的字符串将自动编码为数字。可读查询和输出。这些数字被转换回查询结果中的相应字符串。
这些潜在的问题需要考虑
如果使枚举值看起来像数字则很容易将文字值与其内部索引号混淆。在ORDER BY子句中使用ENUM列需要格外小心
枚举值必须是带引号的字符串字面值。例如你可以像这样创建一个包含ENUM列的表
CREATE TABLE shirts (name VARCHAR(40),size ENUM(x-small, small, medium, large, x-large)
);
INSERT INTO shirts (name, size) VALUES (dress shirt,large), (t-shirt,medium),(polo shirt,small);
SELECT name, size FROM shirts WHERE size medium;
-----------------
| name | size |
-----------------
| t-shirt | medium |
-----------------
UPDATE shirts SET size small WHERE size large;
COMMIT;每个枚举值都有一个索引
美剧列表中的元素被分配索引号从1开始。空字符串的索引值为0。这意味着你可以使用下面的SELECT语句来查找分配了无效ENUM值的行
mysql SELECT * FROM tbl_name WHERE enum_col0;NULL值的索引为NULL。这里的术语索引是指枚举值列表中的位置。它与表索引无关。
一个ENUM列最多可以有65,535个不同的元素。
如果在数字上下文中检索ENUM值则返回列值的索引。例如你可以像这样从ENUM列中检索数值
mysql SELECT enum_col0 FROM tbl_name;SUM()或AVG()等函数需要一个数字参数必要时将参数转换为数字。对于ENUM值在计算中使用索引号。
创建表时表定义中的ENUM成员值中的尾随空格将自动删除。
ENUM列可以被分配一个字符集和排序规则。对于二进制或区分大小写的排序在向列赋值时要考虑大小写。
我们强烈建议不要使用数字作为枚举值因为它不会比适当的TINYINT或SMALLINT类型节省存储空间而且如果不正确地引用ENUM值很容易混淆字符串和底层数字值。如果确实使用数字作为枚举值请始终将其括在引号中。如果省略引号则将该数字视为索引。
在某些情况下枚举值也可以是空字符串或NULL
如果在ENUM中插入一个无效的值则将插入空字符串作为一个特殊的错误值。该字符串的索引是0。如果启用了严格SQL模式尝试插入无效的ENUM值将导致错误。如果将ENUM列声明为允许NULL则NULL值是该列的有效值默认值为NULL。如果一个ENUM列被声明为NOT NULL它的默认值是允许值列表的第一个元素。
ENUM值根据它们的索引号排序索引号取决于枚举成员在列规范中列出的顺序。空字符串排在非空字符串之前NULL值排在所有其他枚举值之前。为了防止在ENUM列上使用ORDER BY子句时出现意外结果请使用以下技术之一
按字母顺序指定ENUM列表通过编码ORDER BY CAST(col AS CHAR)或ORDER BY CONCAT(col)确保列按词法排序而不是按索引号排序。
枚举值不能是表达式即使是计算结果为字符串值的表达式。例如这个CREATE TABLE语句不能工作因为CONCAT函数不能用来构造枚举值
CREATE TABLE sizes (size ENUM(small, CONCAT(med,ium), large)
);也不能将用户变量用作枚举值。这对语句不起作用
SET mysize medium;CREATE TABLE sizes (size ENUM(small, mysize, large)
);SET
SET是一个字符串对象可以有零个或多个值每个值必须从创建表时指定的允许值列表中选择。由多个集合成员组成的SET列值用逗号分隔。这样做的结果是SET成员值本身不应该包含逗号。
一个SET列最多可以有64个不同的成员。
定义中的重复值会导致警告如果启用了严格的SQL模式则会导致错误。
创建表时表定义中的SET成员值中的尾随空格将自动删除。
检索时将使用列定义中使用的字母来显示存储在SET列中的值。注意SET列可以分配一个字符集和排序规则。对于二进制或区分大小写的排序在向列赋值时要考虑大小写。
MySQL以数字方式存储SET值存储值的低位对应于第一个SET成员。如果在数字上下文中检索SET值则检索到的值具有与组成列值的集合成员相对应的位集。例如你可以像这样从SET列中检索数值
mysql SELECT set_col0 FROM tbl_name;如果将一个数字存储在SET列中则在该数字的二进制表示中设置的位决定列值中的集合成员。对于指定为SET(abcd)的列成员具有以下十进制和二进制值。
SET成员十进制值二进制值‘a’10001‘b’20010‘c’40100‘d’81000
如果将值9赋给这一列那么在二进制中就是1001因此第一个和第四个SET值成员a和d被选中结果值为a,d。
对于包含多个SET元素的值在插入值时元素的排列顺序无关紧要。给定元素在值中列出多少次也无关紧要。当稍后检索该值时值中的每个元素都会出现一次并根据表创建时指定的顺序列出元素。假设指定一个列为SET(abcd)
mysql CREATE TABLE myset (col SET(a, b, c, d));mysql INSERT INTO myset (col) VALUES
- (a,d), (d,a), (a,d,a), (a,d,d), (d,a,d);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0然后所有这些值在检索时显示为a,d
mysql SELECT col FROM myset;
------
| col |
------
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
------
5 rows in set (0.04 sec)如果将SET列设置为不支持的值则忽略该值并发出警告
mysql INSERT INTO myset (col) VALUES (a,d,d,s);
Query OK, 1 row affected, 1 warning (0.03 sec)mysql SHOW WARNINGS;
---------------------------------------------------------
| Level | Code | Message |
---------------------------------------------------------
| Warning | 1265 | Data truncated for column col at row 1 |
---------------------------------------------------------
1 row in set (0.04 sec)mysql SELECT col FROM myset;
------
| col |
------
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
------
6 rows in set (0.01 sec)如果启用了严格SQL模式尝试插入无效的SET值将导致错误。
SET值按数字排序。NULL值排在非NULL SET值之前。
SUM()或AVG()等函数需要一个数字参数必要时将参数转换为数字。对于SET值强制转换操作导致使用数值。
通常您使用FIND_IN_SET()函数或LIKE操作符搜索SET值
mysql SELECT * FROM tbl_name WHERE FIND_IN_SET(value,set_col)0;
mysql SELECT * FROM tbl_name WHERE set_col LIKE %value%;第一个语句查找set_col包含值集成员的行。第二个类似但不相同它查找set_col在任何地方包含值的行即使是作为另一个集合成员的子字符串。
还允许下列声明
mysql SELECT * FROM tbl_name WHERE set_col 1;
mysql SELECT * FROM tbl_name WHERE set_col val1,val2;第一个语句查找包含第一个集合成员的值。第二步寻找完全匹配的。对第二种类型的比较要小心。将集合值与val1,val2进行比较所返回的结果与将集合值与val2,val1进行比较所返回的结果不同。您应该按照列定义中列出的顺序指定值。
要确定SET列的所有可能值请使用SHOW COLUMNS FROM tbl_name LIKE set_col并在输出的Type列中解析SET定义。
字符串字面量
字符串是一个字节或字符序列由单引号或双引号字符组成
a string
another string相邻的带引号的字符串被连接成一个字符串。下面几行是等价的
a string
a string如果启用了ANSI_QUOTES SQL模式则字符串字面值只能在单引号内加引号因为在双引号内加引号的字符串将被解释为标识符。
二进制字符串是一串字节。每个二进制字符串都有一个名为binary的字符集和排序规则。非二进制字符串是一串字符。它具有binary以外的字符集和与该字符集兼容的排序规则。
对于这两种类型的字符串比较都是基于字符串单元的数值。对于二进制字符串单位是字节比较使用数字字节值。对于非二进制字符串单位是字符一些字符集支持多字节字符比较使用数字字符代码值。
字符串字面量可以有一个可选的字符集引入器和COLLATE子句将其指定为使用特定字符集和排序规则的字符串
[_charset_name]string [COLLATE collation_name]您可以使用Nliteral或nliteral在本地字符集中创建字符串。以下语句是等价的
SELECT Nsome text;
SELECT nsome text;
SELECT _utf8some text;在字符串中除非启用NO_BACKSLASH_ESCAPES SQL模式否则某些序列具有特殊含义。这些序列中的每一个都以反斜杠开头称为转义字符。MySQL识别的转义序列如下表所示。对于不在表中的所有其他转义序列将忽略反斜杠。 要将二进制数据插入字符串列应该用转义序列表示某些字符。其中反斜杠和用于引用字符串的引号字符是必须转义的。
在编写应用程序时任何可能包含这些特殊字符的字符串在作为数据值发送给MySQL服务器的SQL语句中使用之前都必须进行适当的转义。你可以通过两种方式做到这一点
使用转义特殊字符的函数处理字符串。在C程序中您可以使用mysql_real_escape_string_quote() C API函数来转义字符。作为显式转义特殊字符的替代方案许多MySQL API提供占位符功能使您能够将特殊标记插入语句字符串然后在发出语句时将数据值绑定到它们。在这种情况下API会为您转义值中的特殊字符。
JSON数据类型
如有需要请看这里。
空间数据类型
如有需要请看这里。
数据类型的默认值
数据类型定义可以具有显式或隐式的默认值。数据类型规范中的DEFAULT value子句显式地指示列的默认值
CREATE TABLE t1 (i INT DEFAULT -1,c VARCHAR(10) DEFAULT ,price DOUBLE(16,2) DEFAULT 0.00
);SERIAL DEFAULT VALUE是一个特例。在整型列的定义中它是NOT NULL AUTO_INCREMENT UNIQUE的别名。
MySQL 8.0.13的显式默认值处理
DEFAULT子句中指定的默认值可以是文字常量或表达式。表达式必须放在括号里
CREATE TABLE t1 (-- literal defaultsi INT DEFAULT 0,c VARCHAR(10) DEFAULT ,-- expression defaultsf FLOAT DEFAULT (RAND() * RAND()),b BINARY(16) DEFAULT (UUID_TO_BIN(UUID())),d DATE DEFAULT (CURRENT_DATE INTERVAL 1 YEAR),p POINT DEFAULT (Point(0,0)),j JSON DEFAULT (JSON_ARRAY())
);唯一的例外是对于TIMESTAMP和DATETIME列您可以指定CURRENT_TIMESTAMP函数作为默认值而不需要用括号括起来。
BLOB、TEXT、GEOMETRY和JSON数据类型只能在将值写入表达式时才可以分配默认值即使表达式值是文字
这是允许的文字默认指定为表达式:
CREATE TABLE t2 (b BLOB DEFAULT (abc));这会产生一个错误默认文本没有指定为表达式:
CREATE TABLE t2 (b BLOB DEFAULT abc);表达式默认值必须遵循以下规则。如果表达式包含不允许的结构则会发生错误
允许使用文字、内置函数和操作符。不允许使用子查询、参数、变量、存储函数和可加载函数。表达式默认值不能依赖于具有AUTO_INCREMENT属性的列。一个列的表达式默认值可以引用其他表列但引用生成的列或具有表达式默认值的列必须引用表定义中较早出现的列。也就是说表达式默认值不能包含对生成的列或具有表达式默认值的列的前向引用。排序约束也适用于使用ALTER TABLE对表列进行重新排序。如果结果表的表达式默认值包含对生成的列或具有表达式默认值的列的前向引用则语句将失败。
对于CREATE TABLE ... LIKE 和CREATE TABLE ... SELECT语句目标表保留原始表中的表达式默认值。
如果一个表达式的默认值引用了一个不确定的函数那么任何导致计算该表达式的语句对于基于语句的复制都是不安全的。这包括INSERT和UPDATE之类的语句。在这种情况下如果禁用二进制日志记录则该语句将正常执行。如果启用二进制日志记录binlog_format设置为STATEMENT则记录并执行该语句但会在错误日志中写入警告消息因为复制从节点可能会偏离。当binlog_format设置为MIXED或ROW时语句会正常执行。
当插入新行时可以通过省略列名或将列指定为DEFAULT来插入带有表达式的列的默认值
mysql CREATE TABLE t4 (uid BINARY(16) DEFAULT (UUID_TO_BIN(UUID())));
mysql INSERT INTO t4 () VALUES();
mysql INSERT INTO t4 () VALUES(DEFAULT);
mysql SELECT BIN_TO_UUID(uid) AS uid FROM t4;
--------------------------------------
| uid |
--------------------------------------
| f1109174-94c9-11e8-971d-3bf1095aa633 |
| f110cf9a-94c9-11e8-971d-3bf1095aa633 |
--------------------------------------并非所有存储引擎都允许表达式默认值。如果没有则会出现ER_UNSUPPORTED_ACTION_ON_DEFAULT_VAL_GENERATED错误。
如果默认值的计算结果与声明的列类型不同则根据通常的MySQL类型转换规则对声明的类型进行隐式强制转换。
MySQL 8.0.13之前的显式默认处理
除了一个例外DEFAULT子句中指定的默认值必须是一个文字常量它不能是函数或表达式。例外的是对于TIMESTAMP和DATETIME列您可以指定CURRENT_TIMESTAMP作为默认值。
不能为BLOB、TEXT、GEOMETRY、JSON数据类型分配默认值。
如果默认值的计算结果与声明的列类型不同则根据通常的MySQL类型转换规则对声明的类型进行隐式强制转换。
隐式默认值处理
如果数据类型定义中没有明确的DEFAULT值MySQL将按照如下方式确定默认值
如果列可以接受NULL作为值则用显式DEFAULT NULL子句定义该列。如果列不能以NULL作为值MySQL定义该列时没有显式的DEFAULT子句。对于没有显式DEFAULT子句的NOT NULL列如果INSERT或REPLACE语句不包含该列的值或者UPDATE语句将该列设置为NULL, MySQL将根据当时有效的SQL模式处理该列 如果启用了严格SQL模式事务性表就会出现错误语句就会回滚。对于非事务性表会发生错误但如果多行语句的第二行或后续行发生错误则会插入前面的行。如果没有启用严格模式MySQL将列设置为列数据类型的隐式默认值。
隐式默认值定义如下
对于数字类型默认值是0但对于使用AUTO_INCREMENT属性声明类型默认值是序列中的下一个值。对于TIMESTAMP以外的日期和时间类型默认值是该类型的适当零值。如果启用了explicit_defaults_for_timestamp系统变量那么对于TIMESTAMP也是如此。否则对于表中的第一个TIMESTAMP列默认值是当前日期和时间。对于ENUM以外的字符串类型默认值为空字符串。对于ENUM默认值是第一个枚举值。
NULL
NULL值表示“无数据”。NULL可以写成任何字母形式。
请注意NULL值与数字类型的0或字符串类型的空字符串等值不同。对于使用LOAD DATA或SELECT…INTO OUTFILE执行的文本文件导入或导出操作。, NULL由\n序列表示。对于ORDER BY排序对于升序排序NULL值排在其他值之前对于降序排序NULL值排在其他值之后。
