打开主菜单

目录

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字节

整型编辑

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

TIME类型仅表示时间。格式'HH:MM:SS',或者时间间隔。例如: -02:00:00表示"过去2个小时"). 表示范围为: '-838:59:59' => '838:59:59'. 3字节长。

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


MySQL不会自动把时间补上今天的日期。


基于Unix的时间戳,开始于Unix Epoch,上限到 (2038年).

mysql> SET time_zone = '+00:00'; -- GMT
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT FROM_UNIXTIME(-1);
+-------------------+
| FROM_UNIXTIME(-1) |
+-------------------+
| NULL              |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(0); -- "Epoch"
+---------------------+
| FROM_UNIXTIME(0)    |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2145916799);
+---------------------------+
| FROM_UNIXTIME(2145916799) |
+---------------------------+
| 2037-12-31 23:59:59       |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2145916800);
+---------------------------+
| FROM_UNIXTIME(2145916800) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

获得当前日期+时间(date + time)函数:now():在执行开始时求值

+---------------------+
| now() |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+

sysdate() 日期时间函数跟 now() 类似,但sysdate() 在函数执行时动态得到值。

获得当前时间戳函数:current_timestamp()

日期时间转为字符串的函数date_format(date,format), time_format(time,format):

select date_format('2013-03-09','%Y-%c-%d');

字符串转为日期时间的函数:

select STR_TO_DATE('2013-03-09','%Y-%c-%d');
%W 星期名字(Sunday……Saturday)  
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)  
%Y 年, 数字, 4 位  
%y 年, 数字, 2 位  
%a 缩写的星期名字(Sun……Sat)  
%d 月份中的天数, 数字(00……31)  
%e 月份中的天数, 数字(0……31)  
%m 月, 数字(01……12)  
%c 月, 数字(1……12)  
%b 缩写的月份名字(Jan……Dec)  
%j 一年中的天数(001……366)  
%H 小时(00……23)  
%k 小时(0……23)  
%h 小时(01……12)  
%I 小时(01……12)  
%l 小时(1……12)  
%i 分钟, 数字(00……59)  
%r 时间,12 小时(hh:mm:ss [AP]M)  
%T 时间,24 小时(hh:mm:ss)  
%S 秒(00……59)  
%s 秒(00……59)  
%p AM或PM  
%w 一个星期中的天数(0=Sunday ……6=Saturday )  
%U 星期(0……52), 这里星期天是星期的第一天  
%u 星期(0……52), 这里星期一是星期的第一天  
%% 一个文字“%”。

to_days(date)是从公元0年1月0日开始的天数:

select to_days('0000-00-01'); -- 1

from_days(days)是给出天数返回日期:

mysql> select from_days(1000);
+-----------------+
| from_days(1000) |
+-----------------+
| 0002-09-27      |
+-----------------+
1 row in set (0.00 sec)

时间、秒)转换函数:time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05'); -- 3605
select sec_to_time(3605); -- '01:00:05'

拼凑日期、时间函数:makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2001,31); -- '2001-01-31'
select makedate(2001,32); -- '2001-02-01'
select maketime(12,15,30); -- '12:15:30'

Unix 时间戳、日期)转换函数

unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)

为日期增加一个时间间隔:date_add()、日期减去一个时间间隔date_sub():

select date_add(@dt, interval 1 quarter);

日期、时间相减函数:datediff(date1,date2), timediff(time1,time2)

时间戳(timestamp)转换、增、减函数:

timestamp(date) -- date to timestamp
timestamp(dt,time) -- dt + time
timestampadd(unit,interval,datetime_expr) --
timestampdiff(unit,datetime_expr1,datetime_expr2) --

时区(timezone)转换函数:

convert_tz(dt,from_tz,to_tz)

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"

类型转换与字符串拼接函数编辑

CAST(expr AS type)
convert(XX,TYPE)
CONCAT(str1,str2,...) # 可以是数值作为参数;如果有一个参数为NULL则结果为NULL
CONCAT_WS(separator,str1,str2,...) #concat with separator 带分隔符
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
LPAD(str,len,padstr),RPAD(str,len,padstr):字符串左/友填充函数,支持整数类型的填充

参考文献编辑