Shulou(Shulou.com)05/31 Report--

MySQL 5.5 how to use UNIX_TIMESTAMP to achieve partition, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Sql code

Show variables like'% partition%'

If the system outputs the following results, congratulations, MySQL supports partitioning!

Sql code

Variable_name value

Have_partitioning yes

Sql code

CREATE TABLE quarterly_report_status (

Report_id INT NOT NULL

Report_status VARCHAR (20) NOT NULL

Report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

)

PARTITION BY RANGE (UNIX_TIMESTAMP (report_updated)) (

PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP ('2011-01-01 00-01 00 UNIX_TIMESTAMP))

PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP ('2011-02-01 00UNIX_TIMESTAMP))

PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP ('2011-03-01 00UNIX_TIMESTAMP))

PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP ('2011-04-01 00UNIX_TIMESTAMP))

PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP ('2011-05-01 00UNIX_TIMESTAMP))

PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP ('2011-06-01 00UNIX_TIMESTAMP))

PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP ('2011-07-01 00 UNIX_TIMESTAMP))

PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP ('2011-08-01 00UNIX_TIMESTAMP))

PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP ('2011-09-01 00UNIX_TIMESTAMP))

PARTITION p9 VALUES LESS THAN (MAXVALUE)

);

The official document says that starting from MySQL 5.1.43, no date types except TIMESTAMP are accepted!

Although I didn't try, as to whether you believe it or not, I did!

Explain Partitions SQL test to see if it works:

Sql code

Explain partitions select * FROM quarterly_report_status Q where q.report_updatedSELECT FROM_UNIXTIME (1249488000,'% Y% m% d')

-> 20071120

Mysql > SELECT FROM_UNIXTIME (1249488000,'Y year m month d')

-> November 20, 2007

UNIX_TIMESTAMP () is the opposite of the time function.

UNIX_TIMESTAMP (), UNIX_TIMESTAMP (date)

If it is called without an argument, a Unix timestamp (seconds after '1970-01-01 00 GMT is returned) is returned as an unsigned integer. If you call UNIX_TIMESTAMP () with date, it returns the parameter value as the number of seconds after '1970-01-01 00 GMT. Date can be a DATE string, a DATETIME string, a TIMESTAMP, or a number in YYMMDD or YYYMMDD format at local time.

For example:

Mysql > SELECT UNIX_TIMESTAMP (); (execution makes time: 2009-08-06 10:10:40)

-> 1249524739

Mysql > SELECT UNIX_TIMESTAMP ('2009-08-06')

-> 1249488000

SELECT * FROM `student` WHERE regTime > UNIX_TIMESTAMP (curdate ())

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.