MySql - 数据类型

1：数据类型分类
MySql的数据类型大致可以分为三类：

• 数值型
• 字符型
• 时间和日期

2：各个数据类型的存储大小和取值范围

数字型：

定点数

定点数就是小数点前面的数字个数和小数点后面的数字个数都确定的数字。
语法为

DECIMAL(M, N)

M: 所有的数字总个数
N: 小数点后面的数字个数

DECIMAL(5, 2) 可表示的范围为 `-999.99`to`999.99`

对于定点数，数据库存的是确切值，而不是近似值，这点与浮点数不同。

确切值，就是指数据库存的时候不会做四舍五入。你给的是什么值，在数据库里面存的就是什么值。

但是，当给的数超过了设置的位数，存入数据库怎么处理，依赖于具体的操作系统，但是一般来说会直接截掉超出位数的部分。

浮点数：

语法：

FLOAT(M, N), DOUBLE(M, N)

M: 总的数字个数
N: 小数点后面的数字个数
例如：

FLOAT(7, 4)可以表示`-999.9999`

浮点数的问题：

对于定义了FLOAT(7, 4)的数值，如果数字是999.00009，因为超过了定义的位数，当存入数据库的时候，MySql会进行四舍五入，所以最后在数据库里得到的数字是999.0001。

所以，如果你对数据库里面的浮点数做比较，常常可能会出问题，因为数据库在存浮点数的时候存的是一个近似值，而不是确切值。

---

字符型：

CHAR vs VARCHAR

char：char是定长，如果存入的数据不够定义的那么长会用空格补全。但是读取数据的时候，会自动把空格去掉。

varchar: varchar是变长，在数据库里所占的位数根据存入的数据字符长度而变化。在读取数据的时候，也不会自动把空格去掉。

下图很直观地展示了char和varchar在存储的时候的区别：

定义字符型列

CREATE  TABLE t ( c1 VARCHAR(20)  CHARACTER  SET utf8, 
c2 TEXT  CHARACTER  SET latin1 COLLATE latin1\_general\_cs );

定义字符型的时候，一般根据你的需求定义字符集。通过"CHARSET SET"或者"charset".

枚举类型

CREATE  TABLE shirts (  name  VARCHAR(40), 
size ENUM('x-small',  'small',  'medium',  'large',  'x-large')  );

可以通过"enum_col"来获取枚举数据的index。但是，注意，枚举的index不是数据库表的index，二者之间没有关系。

上图展示了枚举类型的index。空字符串的index都是0，NULL的index为NULL。

枚举的排序
默认情况下，枚举的排序是按照枚举的index。很多时候默认排序无法满足你的需求，为了避免这种情况：
1：可以使用ORDER BY CAST或者ORDER BY CONCAT。
2：或者按照字母顺序存入枚举数据。

数字型的枚举
使用数字（1）或者数字字面量（"1"）作为枚举，都是不推荐的做法。原因我们通过以下的一个例子来说明。

1: 假设我们定义了一个数字字面量的枚举类型的列：numbers

numbers ENUM('0','1','2')

2: 我们依次存入：2, '2', 3
3: 那最后我们在数据库里得到的数据是：'1', '2', '2'

我们之所以得到'1', '2', '2'的原因是：

1：当我们存入2的时候，2被解释成枚举的index. 当index=2的时候，对应的枚举值是'1'，所以存入数据库里面第一个值是'1'。
2：当我们存入'2'的时候，'2'是ENUM('0','1','2')里的一个合法值，所以被正确地存入数据库，得到'2'。
3：当我们存入'3'的时候，'3'不在枚举的范围内，所以被当成了index。当index=3时，对应的枚举值是'2'。

通过以上这个例子，我们可以看到如果把数字字面量作为枚举，将会产生意想不到的bug。所以，最好不要使用数字字面量作为枚举。

Set类型
语法：

SET('one', 'two')

读取Set数据：

SELECT  set_col+0  FROM  tbl_name;

---

时间和日期：

1：时间和日期有哪三种类型？

DATE, DATETIME, TIMESTAMP

DATE: 使用"yyyy-mm-dd"的格式表示。DATE只能表示日期，不能有具体的时间（点钟）。取值范围为'1000-01-01' ~ '9999.12.31'。

DATETIME: 使用"yyyy-mm-dd hh:mm:ss"格式表示。DATETIME表示既有日期又又具体点钟的时间。可取值范围为
'1000-01-01 00:00:00' ~ '9999.12.31 23:59:59'。

TIMESTAMP: TIMESTAMP可用来表示带有时区的时间。它的可取值范围为'1970-01-01 00:00:01'UTC ~ '2038-01-19 03:14:07'UTC。

2：时间和日期有哪些注意的点?

DATE，DATETIME都是不带时区的，TIMESTAMP是带时区的。当存TIMESTAMP数据的时候，MySql把当前的时区转换为UTC；读取TIMESTAMP数据的时候，又从UTC时区转化为当前的时区。

3：mySql对于不合法的日期和时间是怎么处理的？

对于不合法的日期，例如'2000-45-50'，或者'2004-04-31'，MySql会把他们转化为'0000-00-00'(非严格模式)，或者直接得到error（严格模式）

4：mySql对2位数的年怎么处理？

• 00 - 69会被转化为 2000 - 2069
• 70 - 99会被转化为 1970 - 1999

insert或者update时数据的默认值
1：可以在定义列的时候明确地给出默认值：

 create table t1(age   INT  DEFAULT  0);

2: 如果没有在定义的时候给出明确的默认值：

如果在insert或者update的时候给不能是null的列，没有提供明确的值，那么MySql会按照下面的逻辑处理：

• 严格模式下，

  • 如果是事务表（transaction，会报错，不但本条操作不成功，所有操作会回滚（roll back）。
  • 如果不是事务表，会报错；本条操作不会成功。但是，如果这条insert或者update前面还有别的操作，那么前面的操作会成功。

• 非严格模式下，

  • 本条操作会成功，MySql会设置这列为此数据类型相应的默认值。

那么，不同的数据类型的默认值是什么呢？
不同数据类型的默认值

数值类型：默认值为0。如果设置了AUTO_INCREMENT，则是自动增长的值。
DATE，DATETIME：默认值是对应的'0'值。
TIMESTAMP：

如果开启了explicit_defaults_for_timestamp, 默认值也是对应的'0'值。

如果没有开启explictit_defaults_for_timestamp, 默认值就是此刻的时间值。

除了ENUM之外的字符串类型： 默认值是空字符串
ENUM类型：默认值是ENUM的第一个元素值。