Instructions on whether to use INT or DateTime for MySQL time fields_PHP tutorial

今天解析DEDECMS时发现deder的MYSQL时间字段，都是用

`senddata` int(10) unsigned NOT NULL DEFAULT '0';

随后又在网上找到这篇文章，看来如果时间字段有参与运算，用int更好，一来检索时不用在字段上转换运算，直接用于时间比较！二来如下所述效率也更高。

归根结底：用int来代替data类型，更高效。

环境：

Windows XP
PHP Version 5.2.9
MySQL Server 5.1

第一步、创建一个表date_test（非定长、int时间）

CREATE TABLE `test`.`date_test` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` INT NOT NULL ,
`some_content` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

第二步、创建第二个表date_test2（定长、int时间）

CREATE TABLE `test`.`date_test2` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` INT NOT NULL ,
`some_content` CHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

第三步、创建第三个表date_test3（varchar、datetime时间）

CREATE TABLE `test`.`date_test3` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` DATETIME NOT NULL ,
`some_content` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

第四步、创建第四个表date_test3（char、datetime时间）

CREATE TABLE `test`.`date_test4` (
`id` INT NOT NULL AUTO_INCREMENT ,
`start_time` DATETIME NOT NULL ,
`some_content` CHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

ok, now we start testing, the environment is php, first insert one million pieces of data into each table. When inserting, it is divided into 200 times, and 5,000 items are entered into the database each time.

Table 1 execution record: Page running time: 26.5997889042 seconds. An interesting phenomenon was discovered during insertion: the result of SELECT count( id ) FROM `date_test` WHERE 1 is 100w, but the result of directly selecting * from `date_test` is 1,000,374 results. (I later saw that this was a value that might be close, see MySQL FAQ 3.11).

Table 2 execution records: Page running time: 62.3908278942 seconds, this time there are 1,000,066 records.

Table 3 execution record: Page running time: 30.2576560974 seconds, this time there are 1,000,224 entries.

Table 4 execution record: Page running time: 67.5393900871 seconds, this time: 1,000,073 records.

Now add indexes to the start_time fields of the four tables one by one.

Test the update of four tables, update 100 records respectively, and record the time:

Table 1: Page running time: 2.62180089951 seconds (non-fixed length, int time)

Table 2: Page running time: 2.5475358963 seconds (fixed length, int time)

Table 3: Page running time: 2.45077300072 seconds (varchar, datetime time)

Table 4: Page running time: 2.82798409462 seconds (char, datetime time)

Test the reading of four tables, select 100 random records respectively, query based on the primary key id, and record the time:

Table 1: Page running time: 0.382651090622 seconds (non-fixed length, int time)

Table 2: Page running time: 0.542181015015 seconds (fixed length, int time)

Table 3: Page running time: 0.334048032761 seconds (varchar, datetime time)

Table 4: Page running time: 0.506206989288 seconds (char, datetime time)

Test the reading of four tables, select 10 random records respectively, query with star_time as the condition, and record the time:

Table 1: Page running time: 30.1972880363 seconds (non-fixed length, int time)

Table 2: Page running time: 65.1926910877 seconds (fixed length, int time)

Table 3: Page running time: 39.7210869789 seconds (varchar, datetime time)

Table 4: Page running time: 70.4632740021 seconds (char, datetime time)

Because the amount is relatively small, we assume that even small changes are meaningful.

Conclusion:

Under large data volumes, if there are a large number of queries such as select * from table where time>XX, it makes sense to use int for datetime in MySQL5.1.

http://www.bkjia.com/PHPjc/325104.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/325104.htmlTechArticleWhen parsing DEDECMS today, I found that the MYSQL time fields of deder are all using `senddata` int(10) unsigned NOT NULL DEFAULT '0'; Then I found this article on the Internet. It seems that if the time field has...