本文详细介绍了 MySQL 中的数字类型，包括整型、定点数和浮点数，以及它们的精度、范围和显示宽度。此外，还涵盖了日期时间类型，如 date、datetime 和 timestamp，包括精度、范围以及时间戳的自动初始化和更新。最后，讨论了字符串类型，如 char、varchar、text、enum 和 set，包括字符集、排序规则和长度限制。
一、概述
语法约定
M表明整形类型的最大显示宽度。对定点类型和浮点类型，M表明可以存储的数字数量(精度)。对字符类型，M表明最大长度。M的最大值取决于数据类型。
D应用于定点类型和浮点类型，表明小数点后的数字数量(刻度)。最大可能值为30，但是应该不比M-2大。
fsp应用于time、datetime和timestamp类型，代表精确到小数部分的秒。就是秒的小数部分数字的数量。如果给定fsp值，则一定是0到6。0表示没有小数部分。如果忽略，默认值为0(这和标准sql中的6不同，是为了兼容mysql之前的版本)。
方括号("[" 和 "]")表明定义中的可选部分。
1.数字类型概述
M表明最大显示宽度，最大显示宽度为255，显示宽度与类型的值范围无关。对定点类型和浮点类型来说，最大显示宽度代表可以保存几个数字。
当指定zerofill时，自动添加unsigned属性。
提供unsigned属性，也提供signed属性。但是signed是默认的，所以signed属性并无作用。
serial是bigint unsigned not null auto_increment unique的别名。
serial default value在整形列的定义中是not null auto_increment unique的别名。
警告：当使用减法时，如果其中一个数是unsigned，则结果是unsigned，除非启用no unsigned subtraction模式。
bit[(M)],一个字节类型，M值为0到64，如果忽略M，则M取默认值1。
tinyint[(M)] [unsigned] [zerofill]，一个非常小的整形。有符号时数值范围为(-128,127)。无符号时数值范围为(0,255)。
bool、boolean，这些类型是tinyint(1)的同义词。0被认为是false。非0被认为是true。然而true仅仅是1的别名，false仅仅是0的别名(true不等于2)。
smallint[(M)] [unsigned] [zerofill]，小整形。有符号时数值范围为(-32768,32767)。无符号时数值范围为(0,65535)。
mediumint[(M)] [unsigned] [zerofill]，中整形。有符号时数值范围为(-8388608，8388608)。无符号时数值范围为(0,16777215)。
int[(M)] [unsigned] [zerofill]，正常整形。有符号时数值范围为(-2147483648，2147483647)。无符号时数值范围为(0，4294967295)。
integer[(M)] [unsigned] [zerofill]，此类型是int的同义词。
bigint[(M)] [unsigned] [zerofill]，大整形。有符号时数值范围为(-9223372036854775808，9223372036854775807)。无符号时数值范围为(0，18446744073709551615)。serial是bigint unsigned not null auto_increment unique的别名。关于bigint，有以下注意事项。
所有运算都支持有符号的bigint、double，但不应该使用无符号的大于9223372036854775807 (63 bits)的大整形，除非使用位函数。因为在从bigint转换到double时，结果的后几位可能会错，因为舍入误差。
mysql支持以下bigint使用方式：在bigint列中用整形保存无符号长整形；min(col_name)或者max(col_name),其中col_name为bigiint列；使用+、-、*等操作符时，其中操作数都是整形。
可以将bigint列的准确数值使用字符保存。这种情况下，mysql提供了一个字符到数值的转换，此转换不涉及双精度的表示方式。
当操作数都是整形时，+、-和*操作符使用bigint运算。这意味着，如果两个大整形相乘(或者返回整形的函数的结果)，可能会得到意料外的结果，如果结果大于9223372036854775807。
decimal[(M[,D])] [unsigned] [zerofill]，"精确的"定点数。M是数字总数(精度)，D是小数点后数字数(刻度)。小数点和负数的"-"标记不算在M中。如果D为0，则值没有小数点和小数部分。decimal的最大数字量(M)为65。支持的小数数字量最大为30。如果D忽略，则取默认值0。如果M忽略，则取默认值10。如果标示unsigned，则不允许负值。所有decimal的基础运算(+、-、*、/)都是按照65数字精度运算的。
dec[(M[,D])] [unsigned] [zerofill]、numeric[(M[,D])] [unsigned] [zerofill]、fixed[(M[,D])] [unsigned] [zerofill]，这些类型时decimal的同义词。同义词fixed是为了和其它数据库系统兼容。
float[(M,D)] [unsigned] [zerofill]，一种小(单精度)浮点数。准许的值范围为(-3.402823466E+38，-1.175494351E-38)、0、(1.175494351E-38，3.402823466E+38)。这些是基于ieee标准理论上的限制。实际的范围可能略小，依赖于硬件和操作系统。M是数字的总数，D是小数点后数字数。如果M和D忽略，值在硬件允许的条件下保存。单精度数值大约精确到7个小数位。如果标记unsigned，则不准许负值。使用float可能会出现一些意外的问题，因为在mysql中，所有的运算都是以doube来计算的。
double[(M,D)] [unsigned] [zerofill]，一种正常(双精度)浮点数。准许的值范围为(-1.7976931348623157E+308，-2.2250738585072014E-308)、0、(2.2250738585072014E-308，1.7976931348623157E+308)。这些是基于ieee标准理论上的限制。实际的范围可能略小，依赖于硬件和操作系统。M是数字的总数，D是小数点后数字数。如果M和D忽略，值在硬件允许的条件下保存。双精度数值大约精确到15个小数位。如果标记unsigned，则不准许负值。
double precision[(M,D)] [unsigned] [zerofill]，real[(M,D)] [unsigned] [zerofill]，这些类型是double的同义词。例外：如果real_as_float脚本模式启用，real是float的同义词，而不是double的。
float(p) [unsigned] [zerofill]，一个单精度数。p代表二进制数字的精度，但是mysql仅仅使用这个值来确定结果的数据类型是float还是double。如果p为0到24，结果为float，没有M，没有D。如果p为25到53，结果为double，没有M，没有D。结果的数值范围和前面说的float或者double相同。float(p)语法是为了兼容odbc。
2.日期时间类型概述
对于date、datetime的范围描述，"supported"支持，意思是虽然早些的值可能可以，但是并不保证可以。
Mysql5.6.4之后，支持time、datetime和timestamp值的秒的小数部分，可以精确到微秒(6个数字)。当定义一个包含秒的小数部分的列时，使用语法：type_name(fsp)，type_name可以是time、datetime或timestamp，fsp是小数秒的精度。例如：create table t1 (t time(3), dt datetime(6));。fsp的值，如果给定的话，一定要在0到6之间。值为0表示没有小数部分。如果忽略，默认精度为0(这和标准sql中的6不同，这么做是为了和mysql的之前版本兼容)。
mysql5.6.5引入了日期时间类型的自动初始化和更新扩展。一个表中的所有timestamp列都可以有这些属性，而不是每个表只有一列。另外，这些属性现在对datetime列也可用了。
year(2)数据类型有一定的问题，所以使用前要慎重考虑。从mysql5.6.6起，year(2)被弃用。现有的表中的year(2)列和之前一样，但是新建或修改表的year(2)列会被转换为year(4)。
date，一个日期。支持范围为('1000-01-01'，'9999-12-31')。mysql以'yyyy-mm-dd'格式显示日期值，但也允许以字符或者数字赋值给date列。
datetime[(fsp)]，一个日期和时间的组合。支持范围为('1000-01-01 00:00:00.000000'，'9999-12-31 23:59:59.999999')。mysql以'yyyy-mm-dd hh:mm:ss[.fraction]'format，但也允许以字符或者数字赋值给date列。自 mysql5.6.4起，可选项取值范围为(0,6)的fsp可以用来标示小数秒的精度。0标示没有小数部分。如果忽略，默认精度为0。自mysql5.6.5起，可以使用default、on update列定义子句标示datetime列，自动初始化和更新到现在的日期和时间。
timestamp[(fsp)]，时间戳。范围是('1970-01-01 00:00:01.000000'UTC，'2038-01-19 03:14:07.999999'UTC)。时间戳是以从'1970-01-01 00:00:00' UTC开始的秒数保存的。timestamp不能表示值'1970-01-01 00:00:00'，因为这个值为从时间点开始的0，而0被保留来表示'0000-00-00 00:00:00'，称为0时间戳值。自 mysql5.6.4起，可选项取值范围为(0,6)的fsp可以用来标示小数秒的精度。0标示没有小数部分。如果忽略，默认精度为0。服务器处理timestamp定义时，依赖于explicit_defaults_for_timestamp系统变量。默认时，explicit_defaults_for_timestamp没有启用，服务器以以下方式处理timestamp：除非有其它的指定，表中的第一个timestamp列，如果没有确实指定值，则被定义为自动设置为最近修改的日期和时间。这使得timestamp对于记录插入和更新操作的时间戳非常有帮助。还可以将任何一列timestamp赋值为null来设置为当前时间，除非此列被设置null属性来准允许null值。可以使用DEFAULT CURRENT_TIMESTAMP、ON UPDATE CURRENT_TIMESTAMP列定义子句来指定自动初始化和更新到当前日期时间。如前面所说，默认表中的第一个timestamp列具有上述属性。自mysql5.6.5起，一个表中的所有timestamp都可以定义为具有这些属性。在5.6.5前，每个表最多有一个timestamp列可以有这些属性，但是可以禁用第一个timestamp列而把这些属性给予另外一个timestamp列。如果explicit_defaults_for_timestamp启用，对表中的所有timestamp列就没有自动分配 DEFAULT CURRENT_TIMESTAMP 或者ON UPDATE CURRENT_TIMESTAMP属性。这些属性必须明确的包含在表定义中。而且，所有没有明确声明not null的timestamp列都允许null值。自mysql5.6.6起，explicit_defaults_for_timestamp可用。在5.6.6之前，服务器按照之前讨论的explicit_defaults_for_timestamp禁用的方式处理timestamp。那些行为，现在依然是默认的，因为不标准而从5.6.6起弃用。
year[(2|4)]，2个数字或者4个数字格式的年份。默认时4个数字格式。year(2)和year(4)表现格式不同，但是拥有相同的值范围。使用4个字符格式，值显示如1901到2155和0000,。使用2个字符格式，值显示如70到69，代表年份为1970到2069。mysql以yyyy或yy格式显示year值，但是允许使用字符或者数字给year列赋值。笔记：year(2)数据类型有一定的问题，所以使用前要慎重考虑。从mysql5.6.6起，year(2)被弃用。现有的表中的year(2)列和之前一样，但是新建或修改表的year(2)列会被转换为year(4)。
聚合函数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;
笔记：mysql服务器可以在maxdb模式下运行。这种情况下，timestamp和datetime完全一样。如果这个模式在一个表创建时启用，timestamp列会创建为datetime列。结果，这种列会以datetime格式显示，有相同的值范围，而且也没有自动初始化和更新到当前日期时间。
3.字符串类型概述
mysql以字符单位解释字符列定义中的长度参数。适用于char、varchar和text类型。
很多字符串类型的列定义可以包含指定列的字符集和排序规则的属性。适用于char、varchar、text、enum和set类型。
character set属性指定字符集，collate属性指定字符集的排序规则。例如：
CREATE TABLE t
(
c1 VARCHAR(20) CHARACTER SET utf8,
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);
这个表定义，创建了一列名为c1，c1字符集为utf8，排序规则为utf8的默认排序规则，创建了一列名为c2，c2字符集为latin1,排序规则为区分大小写排序。
charset是 character set的一个同义词。
对一个字符类型指定character set binary属性，会导致列被以对应的二进制数据类型创建。char变为binary，varchar变为varbinary，text变为blob。对于enum和set类型，不出出现此情况，它们会以声明创建。假如使用以下定于指定一个表：
CREATE TABLE t
(
c1 VARCHAR(10) CHARACTER SET binary,
c2 TEXT CHARACTER SET binary,
c3 ENUM('a','b','c') CHARACTER SET binary
);
结果表会有这个定义：
CREATE TABLE t
(
c1 VARBINARY(10),
c2 BLOB,
c3 ENUM('a','b','c') CHARACTER SET binary
);
ascii属性是character set latin1的速记。
unicode属性是character set ucs2的速记。
binary属性是指定列字符集二进制排序的速记。这种情况下，排序和比较是基于字符数字值。
字符列的排序和对比是基于列指定的字符集。对于char、varhcar、text、enum和set数据类型，可以声明一个binary collation或者binary属性，使得排序和对比使用底层字符代码值，而不是词法排序。
[national] char[(M)] [character set charset_name] [collate collation_name]，一种定长字符串，保存时会在右边填充空格到指定长度。M代表列的字符的长度。m的值范围是(0,255)，如果M忽略，则长度为1。笔记：重新取出char类型的值时，结尾空格会被移除，除非启用pad char to full length脚本模式。char是character的速记。national char(或者它的等同的缩写nchar)是定义一个char列应该使用预定义的字符集的标准sql方法。mysql4.1以上使用utf8作为预定义的字符集。 char byte类型是binary类型的别名，这是一个兼容的特性。mysql允许创建一个类型为char(0)的列。这主要在符合旧的应用中依赖于这一列但又并不使用这一列的值时有用。char(0)在需要一个只有两个值的列时也很不错，一个被定义为char(0) null的列只占用一个字节，只有值null和''(空字符串)。
[national] varchar(M)[character set charset_name] [collate collation_name]，一种可变长度字符串。M代表列的字符的最大长度。m的值范围是(0,65535)。varchar的实际最大长度受最大行大小(65535位，由所有列共享)和使用的字符集。例如：utf8字符每个字符可占用到3个字节，那么使用utf8字符集的varchar列可被定义为最大21844个字符。mysql以1个字节或2个字节的长度前缀加上数据来保存varchar值。长度前缀指明值所占字节数。当数据不大于255字节时，varchar列使用1个字节长度前缀，当数据大于255字节时，varchar列使用2个字节长度前缀。笔记：mysql遵从标准sql说明书，不移除varchar值的末尾空格。varchar是character verying的速记。national varhcar是是定义一个varchar列应该使用预定义的字符集的标准sql方法。mysql4.1以上使用 utf8作为预定义的字符集。nvarchar是national varchar的速记。
binary(M)，binary类型和char类型相似，只是存储二进制字节字符串，而不是非二进制字符字符串。M代表列的字节长度。
varbinary(M)，varbinary类型和varchar类型相似，只是存储二进制字节字符串，而不是非二进制字符字符串。M代表列的字节最大长度。
tinyblob，一个最大长度为255(28-1)字节的bolb列。每一个tinybolb列保存时，都使用1个字节的长度前缀，这个前缀指明了值的字节数。
tinytext [character set charset_name] [collate collation_name]，一个最大长度为255(2的8次方-1)字符的text列。如果值中包括了多字节字符，实际的最大长度会小。每一个tinytext列保存时，都使用1个字节的长度前缀，这个前缀指明了值的字节数。
blob[(M)]，一个最大长度为65535(216-1)字节的blob列。每一个blob列保存时，都使用2个字节的长度前缀，这个前缀指明了值的字节数。可选项长度M可以指定此类型。如果这么做，mysql会以足够支持M字节长度的最小的blob类型创建此列。
text[(M)] [character set charset_name] [collate collation_name]，一个最大长度为65535(2的16次方-1)字符的text列。如果值中包括了多字节字符，实际的最大长度会小。每一个text列保存时，都使用2个字节的长度前缀，这个前缀指明了值的字节数。可选项长度M可以指定此类型。如果这么做，mysql会以足够支持M字符长度的最小的text类型创建此列。
mediumblob，一个最大长度为16777215(224-1)字节的blob列。每一个mediumblob列保存时，都使用3个字节的长度前缀，这个前缀指明了值的字节数。
mediumtext [character set charset_name] [collate collation_name]，一个最大长度为16777215(2的24次方-1)字符的text列。如果值中包括了多字节字符，实际的最大长度会小。每一个mediumtext列保存时，都使用3个字节的长度前缀，这个前缀指明了值的字节数。
longblob，一个最大长度为4294967295或者说4GB(232-1)字节的blob列。longblob的实际最大长度依赖于客户端/服务器协议中最大包大小的配置和可用内存。每一个longblob列保存时，都使用4个字节的长度前缀，这个前缀指明了值的字节数。
longtext [character set charset_name] [collate collation_name]，一个最大长度为4294967295或者说4GB(2的32次方-1)字符的text列。如果值中包括了多字节字符，实际的最大长度会小。longtext的实际最大长度依赖于客户端/服务器协议中最大包大小的配置和可用内存。每一个longtext列保存时，都使用4个字节的长度前缀，这个前缀指明了值的字节数。
enum('value1','value2',...)[character set charset_name] [collate collation_name]，一个枚举。一个字符串对象，只能有一个值，从值的列表'value1'、'value2'、...、null或者特殊的错误值''中选择。enum值在内部以数值方式表现。一个enum列最多可以有65535个不同的元素(实际的限制是少于3000。)。一个表所有enum列和set列一起不能有超过255种唯一的元素列表定义。
set，一个集合。一个可以有0个值或者多个值的字符串对象，值必须从值的列表'value1'、'value2'、...中选择。set值在内部以数值方式表现。一个set列最大可以有64个不同的元素。一个表所有enum列和set列一起不能有超过255种唯一的元素列表定义。
扫一扫
1542
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
