Mysql 数据类型详解

1.Decimal

DECIMAL(N,D)
N:总共的位数
D:小数点后的位数

create table items(price DECIMAL(5,2));
INSERT INTO items(price) VALUES(8);
INSERT INTO items(price) VALUES(8342342);
INSERT INTO items(price) VALUES(8.2);
INSERT INTO items(price) VALUES(8.12412);
INSERT INTO items(price) VALUES(812.2);

mysql> select * from items;
+--------+
| price  |
+--------+
|   8.00 |
| 999.99 |
|   8.20 |
|   8.12 |
| 812.20 |
+--------+

由结果可以发现，如果小数点不足两位，则会自动补0；如果小数点后超过2位，会截断；如果传入的数比本身允许的值还要大时，会采用默认允许的最大值999.99

2. Float/Double

	存储	精度
Float	4字节	7位
Double	8字节	15位

 create table test(price float);
 INSERT INTO test(price) VALUES(1231);
 INSERT INTO test(price) VALUES(1231.21);
 INSERT INTO test(price) VALUES(1231.213);
 INSERT INTO test(price) VALUES(1231231.2341);
 
mysql> select * from test;
+---------+
| price   |
+---------+
|    1231 |
| 1231.21 |
| 1231.21 |
| 1231230 |
+---------+

可以发现float只能保证7位数里面的精度，当位数(包括小数点)超过7位，就会产生误差。所以建议使用double。

3.Date/Time/DateTime

#  DATE 年月日 YYYY-MM-DD
#  TIME HH:MM:SS
#  DATETIME  DATE+TIME

 create table people(name varchar(100),birthdate DATE,birthtime TIME,birthdt DATETIME);
 INSERT INTO people(name,birthdate,birthtime,birthdt) VALUES("panda","1998-10-07","10:08:31","1998-10-07 
                    10:08:31");
                    
mysql> select * from people;
+-------+------------+-----------+---------------------+
| name  | birthdate  | birthtime | birthdt             |
+-------+------------+-----------+---------------------+
| panda | 1998-10-07 | 10:08:31  | 1998-10-07 10:08:31 |
+-------+------------+-----------+---------------------+

mysql提供了一些Date 和Time的函数Mysql Documentation

举例：
CURRENT_DATE()
CURRENT_TIME()
CURRENT_TIMESTAMP()
DAYOFMONTH()	Return the day of the month (0-31)
DAYOFWEEK()	 	Return the weekday index of the argument
DAYOFYEAR()		Return the day of the year (1-366)
DATE_FORMAT()   Format date as specified

INSERT INTO people(name,birthdate,birthtime,birthdt) VALUES("handsome",CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP());

mysql> select * from people;
+----------+------------+-----------+---------------------+
| name     | birthdate  | birthtime | birthdt             |
+----------+------------+-----------+---------------------+
| panda    | 1998-10-07 | 10:08:31  | 1998-10-07 10:08:31 |
| handsome | 2020-05-17 | 08:20:25  | 2020-05-17 08:20:25 |
+----------+------------+-----------+---------------------+

-------------------------------------------------
# 将2017-01-21 显示为 January 21 2017
mysql> SELECT concat(MONTHNAME(birthdate)," ",DAY(birthdate)," ",YEAR(birthdate)) FROM people;
+---------------------------------------------------------------------+
| concat(MONTHNAME(birthdate)," ",DAY(birthdate)," ",YEAR(birthdate)) |
+---------------------------------------------------------------------+
| October 7 1998                                                      |
| May 17 2020                                                         |
+---------------------------------------------------------------------+


# 将2017-01-21 DATE_FORMAT的形式用显示为 01/21/2017
mysql> SELECT DATE_FORMAT(birthdt,"%d/%m/%Y") FROM people;
+---------------------------------+
| DATE_FORMAT(birthdt,"%d/%m/%Y") |
+---------------------------------+
| 07/10/1998                      |
| 17/05/2020                      |
+---------------------------------+

---

Date Arthmetic
关于日期的计算也在上面的链接中有，如：
DATEDIFF(expr1,expr2)
DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)
INTERVAL是关键字必须要写，expr是具体的数，unit是数的类型
Mysql 中的Temporal Interval Expression and Unit Arguments

# 计算出生到现在多少天了
mysql> SELECT name, birthdate,DATEDIFF(CURRENT_DATE,birthdate) FROM people;
+----------+------------+----------------------------------+
| name     | birthdate  | DATEDIFF(CURRENT_DATE,birthdate) |
+----------+------------+----------------------------------+
| panda    | 1998-10-07 |                             7893 |
| handsome | 2020-05-17 |                                0 |
+----------+------------+----------------------------------+

# 计算出生日期加上2年5个月
mysql> SELECT name,birthdate,DATE_ADD(birthdate,INTERVAL "2-5" YEAR_MONTH) FROM people;
+----------+------------+-----------------------------------------------+
| name     | birthdate  | DATE_ADD(birthdate,INTERVAL "2-5" YEAR_MONTH) |
+----------+------------+-----------------------------------------------+
| panda    | 1998-10-07 | 2001-03-07                                    |
| handsome | 2020-05-17 | 2022-10-17                                    |
+----------+------------+-----------------------------------------------+

# 计算出生日期加上5个月21天
mysql> SELECT name,birthdate,birthdate+INTERVAL 5 MONTH+INTERVAL 21 DAY FROM people;
+----------+------------+--------------------------------------------+
| name     | birthdate  | birthdate+INTERVAL 5 MONTH+INTERVAL 21 DAY |
+----------+------------+--------------------------------------------+
| panda    | 1998-10-07 | 1999-03-28                                 |
| handsome | 2020-05-17 | 2020-11-07                                 |
+----------+------------+--------------------------------------------+

注意： 如上例所示，可以使用date +/- INTERVAL expr unit 来代替DATE_ADD/DATE_SUB来进行连加或连减

4.TIMESTAMP

和DATETIME一样也是YYYY-MM-DD hh:mm:ss 格式

但是和TimeDate的区别：

• DATETIME的范围是’1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ 而TIMESTAMP的范围是’1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC

• 两者存储方式不一样，TIMESTAMP将客户端插入的数值转化成UTC存储，然后读取的时候从UTC在转化成当前时区；DATETIME不做任何改变。
  官方文档中的原话如下所示：

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

所以TIMESTAMP适用于不同时区的存取

TIMESTAMP和DATETIME都有自动初始化和更新的功能
详见Mysql Automatic Initialization and Updating for TIMESTAMP and DATETIME