MySQL/Language/Data Types

维基教科书,自由的教学读本
跳到导航 跳到搜索

不要使用utf8[编辑]

因为 MySQL 的utf8只支持 BMP(基本多文种平面)范围内的 Unicode 字符。也就是,MySQL 的utf8使用三字节表达。使用utf8mb4的字符集,也就是用四字节表达的 UTF-8 编码。

VARCHAR/CHAR[编辑]

VARCHAR是CHARACTER VARYING的缩写. 最大列长上限是65,535字符。实际长度是所存储字符串长度,再加上1或2字节(如果设定最大长度<255则为1字节)用于存储字符串长度。

CHAR(n)类似于varchar(n),但占据固定存储长度。而varchar需要空间来存储实际的文本。

CHAR 类型用于定长字符串,并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从 0-255。比指定长度大的值将被截短,而比指定长度小的值将会用空格作填补。

CHAR 类型与VARCHAR类型,可以使用 BINARY 修饰符。当用于比较运算时,这个修饰符使 CHAR 以二进制方式参于运算,而不是以传统的区分大小写的方式。  

TEXT 与 BLOB[编辑]

BLOB 或 TEXT 列的最大长度是65,535字符。真实长度是存储数据加上2字节(或1字节当长度<255)。比指定类型支持的最大范围大的值将被自动截短。BLOB / TEXT数据并不存储在表的数据文件中。这使得各种操作(INSERT / UPDATE / DELETE / SELECT)涉及BLOB / TEXT数据更慢,但其他操作更快。

TEXT 和 BLOB 类型在分类和比较上存在区别。BLOB 类型区分大小写,而 TEXT 不区分大小写。

其他类型:

  • TINYBLOB/TINYTEXT: 0-255字节
  • MEDIUMBLOB/MEDIUMTEXT: 0 - 16 777 215字节
  • LOGNGBLOB/LONGTEXT:0-4 294 967 295字节

BOOL类型[编辑]

只是TINYINT(1)的别名

整型[编辑]

MySQL 中支持的 5 个主要整数类型: TINYINT,SMALLINT,MEDIUMINT,INT 和 BIGINT。存储长度分别是1、2、3、4、8字节。

可选的显示宽度指示器是对 SQL 标准进行扩展。例如,字段的类型为 INT(6),则包含数字少于 6个数字的值从数据库中检索出来时能够自动地用空格填充。需要注意的是,使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。

字段存储一个超出许可范围的数字,MySQL 会根据允许范围最接近它的一端截短后再进行存储。

MySQL 会在不合规定的值插入表前自动修改为 0。

UNSIGNED 修饰符规定字段只保存正值。

ZEROFILL 修饰符规定 0(不是空格)可以用来补输出的值。使用这个修饰符可以阻止 MySQL 数据库存储负值。

浮点[编辑]

FLOAT 数值类型用于表示单精度浮点数值,DOUBLE 数值类型用于表示双精度浮点数值。

类型也带有附加参数:一个显示宽度指示器和一个小数点指示器。如语句 FLOAT(7,3) 规定显示的值不会超过 7 位数字,小数点后面带有 3 位数字。小数点后面的位数超过允许范围的值,MySQL 会自动将它四舍五入为最接近它的值,再插入它。

decimal[编辑]

形式为decimal(n,m)

例如:decimal(4,2)意味着最大99.99 (而不是9999.99如你期望那样)。

Dates[编辑]

在三种日期类型中,DATETIME, DATE, TIMESTAMP。

  • DATE类型仅有日期数据没有时间数据;DATE值采取 'YYYY-MM-DD' 格式。3字节长。范围1000-01-01/9999-12-31。
  • DATETIME包含日期与时间数据。8字节长。格式为 YYYY-MM-DD HH:MM:SS。范围 1000-01-01 00:00:00/9999-12-31 23:59:59
  • TIMESTAMP也包含日期与时间数据,但仅覆盖1970-2037。4字节长。即1970-01-01 00:00:00 UTC至2038-01-19 03:14:07 UTC 它实际上存储为UTC,显示的结果受session当前的timezone影响。详见MySQL/Date Time

TIME类型仅表示时间。格式'HH:MM:SS',或者时间间隔。例如: -02:00:00表示"过去2个小时"). 表示范围为: '-838:59:59' => '838:59:59'. 3字节长。MySQL不会自动把时间补上今天的日期。

YEAR类型表示年份。1字节长。范围1901/2155

DATETIME或TIMESTAMP的值可以包含秒的小数部分,最高精度为微妙(6位小数)。格式为‘YYYY-MM-DD HH:MM:SS[.fraction]’。对DATETIME值域为‘1000-01-01 00:00:00.000000’至‘9999-12-31 23:59:59.999999’;对于 TIMESTAMP值域为‘1970-01-01 00:00:01.000000’至‘2038-01-19 03:14:07.999999’. 详见MySQL Help Section 11.3.6, “Fractional Seconds in Time Values”.


set 与 enum[编辑]

MySQL 还支持两种复合数据类型 ENUM 和 SET,它们扩展了 SQL 规范。虽然这些类型在技术上是字符串类型,但是可以被视为不同的数据类型。

ENUM 类型因为只允许在集合中取得一个值,有点类似于单选项。表示相互排拆的数据,如人类的性别。ENUM 类型可使用 null 值。字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入值的大小写与集合中的值的大小写不匹配,MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。因为ENUM 类型在系统内部可以存储为数字,并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素,其中一个元素被 MySQL 保留,用来存储错误信息,这个错误值用索引 0 或者一个空字符串表示。

SET 类型从预定义的集合中取得任意数量的多个值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录,MySQL 将会保留合法的元素,除去非法的元素。一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列,这些“位”表示与它相对应的元素。所以 SET 类型中不可能包含两个相同的元素。希望从 SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。


例如:

SET("madam", "mister")    -- 合法的:空集, "madam", "mister", "madam, mister", "mister, madam"

ENUM("madam", "mister")   -- 合法的:空集, "madam" or  "mister"

CREATE TABLE `20121101_t` (    
  `id` int(11) NOT NULL AUTO_INCREMENT,    
  `name` varchar(20) NOT NULL,    
  `cl` set('x','w','r') NOT NULL,    
  `c2` enum('f','d') NOT NULL,    
  PRIMARY KEY (`id`)    
) ENGINE=InnoDB     
   
insert into 20121101_t    
values(null,'a.txt','r,w','d');      
insert into 20121101_t    
values(null,'b.txt','r,w','f');

类型转换函数[编辑]

CAST(expr AS type)
convert(XX,TYPE)

参考文献[编辑]