MySQL数据类型

MySQL数据类型相关知识点

MySQL数据类型
- 数值型
- 字符串型
- 日期和时间类型

1 数值类型

• 严格数值类型
  
  • integer、smallint、decimal、numeric
• 近似数值数据类型
  
  • float、real、double、precision
• 扩展
  
  • tinyint,mediumint,bigint
• 位类型
  
  • bit

如图所示：

==1.1 整数==

支持在类型名称后面的小括号内指定显示宽度。int(5)表示当数值小于5位的时候在数字前面填满宽度。如果不指定，默认是int(11)

==zerofill==就是用0填充的意思，数字位数不够的空间用字符0填满。

==1.2 小数==

• 浮点数
  
  • float（单精度）、double（双精度）
• 定点数
  
  • decimal

• 浮点数如果不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；
• 定点数如果不写精度和标度，则按照默认值 decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错。

==1.3 Bit位==

可以用来存放多位二进制数，范围从 1～ 64，如果不写则默认为 1 位。对于位字段，用bin()或者 hex()函数进行读取。

2 字符串类型

==2.1 char 和 varchar类型==

• 都用来保存 MySQL 中较短的字符串。 char(4)，varchar(4)能存4个==字符==。
• 二者的主要区别在于存储方式的不同：
  
  • CHAR 列的长度固定为创建表时声明的长度； 而 VARCHAR 列中的值为可变长字符串。
  • 在检索的时候， CHAR 列删除了尾部的空格， 而 VARCHAR 则保留这些空格。

==2.2 binary和varbinary类型==

• BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。

当保存 BINARY 值时，在值的最后通过填充“0x00” （零字节）以达到指定的字段定义长度。对于一个 BINARY(3)列，当插入时’a’变为’a\0\0’。

==2.3 enum类型 枚举类型==

它的值范围需要在创建表时通过枚举方式显式指定， 对 1～ 255 个成员的枚举需要 1 个字节存储；对于 255～65535 个成员，需要 2 个字节存储。最多允许有 65535 个成员。

ENUM 类型只允许从值集合中选取单个值，而不能一次取多个值

==2.4 set类型==

Set 和 ENUM 类型非常类似，也是一个字符串对象。除了存储之外，最主要的区别在于 Set 类型一次可以选取多个成员，而 ENUM则只能选一个。

3 日期时间类型

• 如果用来表示年月日，通常用DATE来表示
• 如果用来表示年月日时分秒，通常用DATETIME来表示
• 如果用来表示时分秒，通常用TIME来表示

timestamp的取值范围是19700101080001到2038年的某一天，因此不适合存放比较久远的日期

TIMESTAMP和DATETIME的区别

1. ==TIMESTAMP支持的时间范围较小==，其取值范围从19700101080001到2038年的某个时间，而DATETIME是从1000-01-01 00:00:00到9999-12-31 23:59:59，范围更大。
2. ==表中的第一个TIMESTAMP列自动设置为系统时间==。 如果在一个TIMESTAMP列中插入NULL，则该列值将自动设置为当前的日期和时间。在插入或更新一行但不明确给TIMESTAMP列赋值时也会自动设置该列的值为当前的日期和时间， 当插入的值超出取值范围时，MySQL认为该值溢出，使用“0000-00-00 00:00:00”进行填补。
3. ==TIMESTAMP的插入和查询都受当地时区的影响，更能反应出实际的日期==。而 DATETIME则只能反应出插入时当地的时区，其他时区的人查看数据必然会有误差的。
4. ==TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大。==

MySQL 以 YYYY 格式检索和显示 YEAR 值，范围是 1901～2155。当使用两位字符串表示 年份时，其范围为“00”到“99”。
- “00”到“69”范围的值被转换为 2000～2069 范围的 YEAR 值
- “70”到“99”范围的值被转换为 1970～1999 范围的 YEAR 值。

==到底什么样的格式才能够正确地插入到对应的日期字段中呢？== 面以 DATETIME 为例
1. ==YYYY-MM-DD HH:MM:SS 或 YY-MM-DD HH:MM:SS== 格式的字符串。允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。例如，“98-12-31 11:30:45”、“98.12.31 11+30+45”、“98/12/31 11*30*45”和“98@12@31 11^30^45” 是等价的。对于包括日期部分间割符的字符串值，如果日和月的值小于10，不需要指定两位数。“1979-6-9”与“1979-06-09”是相同的。同样，对于包括时间部分69间割符的字符串值，如果时、分和秒的值小于 10，不需要指定两位数。“1979-10-30 1:2:3”与“1979-10-30 01:02:03”相同。
1. ==YYYYMMDDHHMMSS 或 YYMMDDHHMMSS== 格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，“19970523091528” 和 “970523091528” 被解释为 “1997-05-23 09:15:28” ，但“971122129015”是不合法的（它有一个没有意义的分钟部分） ，将变为“0000-00-00 00:00:00” 。
1. ==YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的数字==，假定数字对于日期类型是有意义的。 例如， 19830905132800和 830905132800被解释为 “1983-09-05 13:28:00” 。数字值应为 6、8、12 或者 14 位长。如果一个数值是 8 或 14 位长，则假定为YYYYMMDD 或 YYYYMMDDHHMMSS 格式，前 4 位数表示年。如果数字 是 6 或 12位长，则假定为 YYMMDD 或 YYMMDDHHMMSS 格式，前 2 位数表示年。其他数字被解释为仿佛用零填充到了最近的长度。
1. ==函数返回的结果==，其值适合 DATETIME、DATE 或者 TIMESTAMP 上下文，例如 NOW()或 CURRENT_DATE。