Skip to content

Instantly share code, notes, and snippets.

@yijia2413 yijia2413/createt_tb.sql
Last active Jan 14, 2019

Embed
What would you like to do?
CREATE TABLE if not exists `test` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`result` VARCHAR(128),
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY id (id))
ENGINE=InnoDB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1
PARTITION BY RANGE ( TO_DAYS(update_time) ) (
-- PARTITION BY hash (TO_DAYS(update_time)) partitions 31;
PARTITION p_first VALUES LESS THAN (TO_DAYS('2019-01-07 00:00:00')),
PARTITION p20190107 VALUES LESS THAN (TO_DAYS('2019-01-08 00:00:00')),
PARTITION p20190108 VALUES LESS THAN (TO_DAYS('2019-01-09 00:00:00'))
);
insert into procinfo (id, result, update_time)
values
(2, 'a', '2019-01-08 11:11:11'),
(3, 'a', '2019-01-07 11:11:11'),
(1, 'a', '2019-01-02 11:11:11'),
-- set unique key, then insert and remove duplicate
insert into tb (col1, col2) values ('123456', 'abc'), ('98767', 'def') ON DUPLICATE KEY UPDATE col1=values(col1), col2=values(col2);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.