Mysql 时间类型字段、外键相关

一. MySQL 中 datetime 和 timestamp 的区别与选择

MySQL 中常用的两种时间储存类型分别是datetime和 timestamp。如何在它们之间选择是建表时必要的考虑。下面就谈谈他们的区别和怎么选择。

1. 区别

1.1 占用空间

类型	占据字节	表示形式	允许最大长度/默认最大长度
datetime	8 字节	yyyy-mm-dd hh:mm:ss	6/0
timestamp	4 字节	yyyy-mm-dd hh:mm:ss	6/0

长度的含义（设置为6）：
在”设计表”中关于datetime类型设置如下：
长度： 最小为0，最大为6，就是显示时间的时候的秒后面的小数点后的位数，如长度为3则表示毫秒，长度为6则表示微妙。
小数点： 只能为0，就算填上别的数值，保存时候也会被改为0，可见只能为0。
默认值：都可以设置为CURRENT_TIMESTAMP；
想为creat_time字段创建操作设置默认值，update_time字段创建操作设置默认值同时更新操作设置为当前时间可以：

CREATE TABLE xxx (
id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘自增主键’,
table_id int(11) DEFAULT NULL COMMENT ‘表id’,
column_name varchar(64) DEFAULT NULL COMMENT ‘列名字’,
data_type varchar(32) DEFAULT NULL COMMENT ‘列类型’,
data_length int(11) DEFAULT NULL COMMENT ‘列长度’,
column_comment varchar(255) DEFAULT NULL COMMENT ‘列备注’,
column_order int(11) DEFAULT NULL COMMENT ‘列排序’,
custom_comment varchar(255) DEFAULT NULL COMMENT ‘自定义备注’,
del_flag int(11) DEFAULT NULL COMMENT ‘是否删除：0未删除，1删除’,
create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1.2 表示范围

类型 表示范围
datetime ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’
timestamp ‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’
timestamp翻译为汉语即"时间戳"，它是当前时间到 Unix元年(1970 年 1 月 1 日 0 时 0 分 0 秒)的秒数。对于某些时间的计算，如果是以 datetime 的形式会比较困难，假如我是 1994-1-20 06:06:06 出生，现在的时间是 2016-10-1 20:04:50 ，那么要计算我活了多少秒钟用 datetime 还需要函数进行转换，但是 timestamp 直接相减就行。

1.3 时区

timestamp 只占 4 个字节，而且是以utc的格式储存， 它会自动检索当前时区并进行转换。

datetime以 8 个字节储存，不会进行时区的检索.

也就是说，对于timestamp来说，如果储存时的时区和检索时的时区不一样，那么拿出来的数据也不一样。对于datetime来说，存什么拿到的就是什么。

还有一个区别就是如果存进去的是NULL，timestamp会自动储存当前时间，而 datetime会储存 NULL。

2 测试

我们新建一个表

插入数据

查看数据，可以看到存进去的是NULL，timestamp会自动储存当前时间，而 datetime会储存NULL

把时区修改为东 9 区，再查看数据，会会发现 timestamp 比 datetime 多一小时

如果插入的是无效的呢？假如插入的是时间戳

结果是0000-00-00 00:00:00，根据官方的解释是插入的是无效的话会转为 0000-00-00 00:00:00，而时间戳并不是MySQL有效的时间格式。

那么什么形式的可以插入呢，下面列举三种

//下面都是 MySQL 允许的形式，MySQL 会自动处理
2016-10-01 20:48:59
2016#10#01 20/48/59
20161001204859

3 选择

如果在时间上要超过Linux时间的，或者服务器时区不一样的就建议选择 datetime。
如果是想要使用自动插入时间或者自动更新时间功能的，可以使用timestamp。
如果只是想表示年、日期、时间的还可以使用 year、 date、 time，它们分别占据 1、3、3 字节，而datetime就是它们的集合。

MYSQL：外键约束（On Delete和On Update）的使用

外键约束（On Delete和On Update）都有Restrict，No Action, Cascade,Set Null属性。

外键约束1–ON DELETE

A.restrict(约束):当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。
B.no action:意思同restrict.即如果存在从数据，不允许删除主数据。
C.cascade(级联):当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表（即包含外键的表）中的记录。
D.set null:当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）

外键约束2–ON UPDATE

A.restrict(约束):当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新。
B.no action:意思同restrict.
C.cascade(级联):当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则也更新外键在子表（即包含外键的表）中的记录。
D.set null:当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null）。

注：NO ACTION和RESTRICT的区别：只有在极个别的情况下会导致区别，前者是在其他约束的动作之后执行，后者具有最高的优先权执行。

设置外键出错了，检查一下外键表和这个表

1. 两个字段的类型或者大小不严格匹配。例如，如果一个是int(10)，那么外键也必须设置成int(10)，而不是int(11)，也不能是tinyint。另外，你还必须确定两个字段是否都为signed或者unsigned，这两字段必须严格地一致匹配。

2. 设置外键的字段没有建立起索引，或者不是一个primary key(主键)。一般，需要建立外键的数据表称为子表，而相关联的数据表称为父表。建立外键时所对应的父表的字段必须要创建索引primary key。

3. 其中一个或者两个表是MyISAM引擎的表。若想要使用外键约束，表必须是InnoDB引擎(实际上，如果两个表都是MyISAM 引擎的，这个错误根本不会发生，但也不会产生外键，只会建立索引)你需要检查表的引擎类型。

4. 外键的名字不能重复。检查该数据库确保外健名字是唯一的，或者你可以在键名后面加上几个随机的字符以测试是否为此原因。

5. 设置了ON DELETE SET NULL，但是相关的键的字段设置成了NOT NULL值。通过修改外键的属性值或者把字段属性设置成allow null来解决。

6. 确保你的Charset和Collate选项在表级和字段级上的一致。

7. 两字段设置的默认值不一致。一个字段设置default为0，一个设置default为null，也是不可行的。

8. ALTER声明中有语法错误

9. mysql中，主表的主键id是从表的外键，主表的主键是不允许为null自增的无符号的，那么从表的外键也必须是无符号的，但是可以不自增（一张表只能有一个自增字段且必须设置为主键），也可以为null（如果此外键设置了ON DELETE/UPDATE SET NULL则此外建必须允许为null）