mysql 時間欄位介紹

標籤：sql   val   optional   procedure   src   inter   while   because   準備   

mysql時間類型大概有5種，如

1、建立資料庫

create table t1 (    id  int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,    d1_data date,    d2_time time,    d3_datatime datetime,    d4_year year,    d5_timestamp TIMESTAMP);

字串方式插入

INSERT  INTO t1 (d1_data,d2_time,d3_datatime,d4_year,d5_timestamp) VALUES(NOW(),NOW(),NOW(),NOW(),NOW());mysql> select * from t1 where id=1\G*************************** 1. row ***************************          id: 1     d1_data: 2018-06-15     d2_time: 13:50:25 d3_datatime: 2018-06-15 13:50:25     d4_year: 2018d5_timestamp: 2018-06-15 13:50:251 row in set (0.00 sec)INSERT  INTO t1 (d1_data,d2_time,d3_datatime,d4_year,d5_timestamp) VALUES(‘2018-06-15‘,‘13:50:25‘,‘2018-06-15 13:50:25‘,‘2018‘,‘2018-06-15 13:50:25‘);mysql> select * from t1 where id=2\G*************************** 1. row ***************************          id: 2     d1_data: 2018-06-15     d2_time: 13:50:25 d3_datatime: 2018-06-15 13:50:25     d4_year: 2018d5_timestamp: 2018-06-15 13:50:251 row in set (0.00 sec)

1.1、date (數字插入)

mysql> help DATEA date. The supported range is ‘1000-01-01‘ to ‘9999-12-31‘. MySQLdisplays DATE values in ‘YYYY-MM-DD‘ format, but permits assignment ofvalues to DATE columns using either strings or numbers.插入時候可以是字串或者數字20180615‘2018-06-15‘

1.2、time (數字插入)

mysql> help TIMEA time. The range is ‘-838:59:59.000000‘ to ‘838:59:59.000000‘. MySQLdisplays TIME values in ‘HH:MM:SS[.fraction]‘ format, but permitsassignment of values to TIME columns using either strings or numbers.An optional fsp value in the range from 0 to 6 may be given to specifyfractional seconds precision. A value of 0 signifies that there is nofractional part. If omitted, the default precision is 0.mysql> INSERT  INTO t1 (d1_data,d2_time,d3_datatime,d4_year,d5_timestamp) VALUES(20180515,135025,‘2018-06-15 13:50:25‘,‘2018‘,‘2018-06-15 13:50:25‘);Query OK, 1 row affected (0.00 sec)mysql> select * from t1 where id=4\G*************************** 1. row ***************************          id: 4     d1_data: 2018-05-15     d2_time: 13:50:25 d3_datatime: 2018-06-15 13:50:25     d4_year: 2018d5_timestamp: 2018-06-15 13:50:251 row in set (0.00 sec)

1.3、 DATETIME (數字插入)

mysql> help DATETIMEA date and time combination. The supported range is ‘1000-01-0100:00:00.000000‘ to ‘9999-12-31 23:59:59.999999‘. MySQL displaysDATETIME values in ‘YYYY-MM-DD HH:MM:SS[.fraction]‘ format, but permitsassignment of values to DATETIME columns using either strings ornumbers.An optional fsp value in the range from 0 to 6 may be given to specifyfractional seconds precision. A value of 0 signifies that there is nofractional part. If omitted, the default precision is 0.Automatic initialization and updating to the current date and time forDATETIME columns can be specified using DEFAULT and ON UPDATE columndefinition clauses, as described inhttp://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html.20180615135025‘2018-06-15 13:50:25‘儲存的是字串mysql> INSERT  INTO t1 (d1_data,d2_time,d3_datatime,d4_year,d5_timestamp) VALUES(20180515,135025,20180615135025,‘2018‘,‘2018-06-15 13:50:25‘);Query OK, 1 row affected (0.01 sec)mysql> select * from t1 where id=5\G*************************** 1. row ***************************          id: 5     d1_data: 2018-05-15     d2_time: 13:50:25 d3_datatime: 2018-06-15 13:50:25     d4_year: 2018d5_timestamp: 2018-06-15 13:50:251 row in set (0.00 sec)

1.4、 year

mysql> help yearReturns the year for date, in the range 1000 to 9999, or 0 for the"zero" date.

1.5、 timestamp (數字插入)

mysql> help timestampA timestamp. The range is ‘1970-01-01 00:00:01.000000‘ UTC to‘2038-01-19 03:14:07.999999‘ UTC. TIMESTAMP values are stored as thenumber of seconds since the epoch (‘1970-01-01 00:00:00‘ UTC). ATIMESTAMP cannot represent the value ‘1970-01-01 00:00:00‘ because thatis equivalent to 0 seconds from the epoch and the value 0 is reservedfor representing ‘0000-00-00 00:00:00‘, the "zero" TIMESTAMP value.儲存的是時間戳記mysql> INSERT  INTO t1 (d1_data,d2_time,d3_datatime,d4_year,d5_timestamp) VALUES(20180515,135025,20180615135025,‘2018‘,20180615135025);Query OK, 1 row affected (0.01 sec)

總結

以上說明 在插入或者更新時候，值可以是 ‘2018-06-06‘ 時間類型字串 也可以是 20180606 數字，都會經過mysql函數處理存入

100w條資料 比較datatime跟timestamp效率

2、 準備100w條資料

drop PROCEDURE  autoinsert;DELIMITER $$create PROCEDURE autoinsert()  BEGIN    DECLARE i int DEFAULT 1;    DECLARE mytime varchar(19) ;    WHILE (i<1000000) DO      set mytime=date_add(now(),interval i second);      INSERT into t1  (d1_data,d2_time,d3_datatime,d4_year,d5_timestamp) VALUES (mytime,mytime,mytime,date_add(now(),interval i second),mytime);      set i=i+1;    END WHILE ;  END $$DELIMITER ;CALL autoinsert();

2.1、datetime 分析：沒有索引情況下：

大概需要0.22s添加索引

mysql> alter table t1 add key (d3_datatime);

2.2、timestamp分析

沒有索引情況下：

需要0.4s左右添加索引情況後：

ALTER TABLE `t1` add key (d5_timestamp);

100w條資料的理論，datetime比timestamp更快點，底層比較應該都是數字，timestamp可能在時間轉換上消耗更好點吧，在加上索引情況下是沒有區別的，從空間上考慮datetime需要8個位元組，而timestamp只要4個位元組。

mysql 時間欄位介紹