分区实践 注意分区名 p2018-01 p2018-02 被解释为同一分区名

# https://dev.mysql.com/doc/refman/5.6/en/partitioning-columns-range.html
'''
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
);
ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired) (
PARTITION p0 VALUES LESS THAN ('1970-01-01'),
PARTITION p1 VALUES LESS THAN ('1980-01-01'),
PARTITION p2 VALUES LESS THAN ('1990-01-01'),
PARTITION p3 VALUES LESS THAN ('2000-01-01'),
PARTITION p4 VALUES LESS THAN ('2010-01-01'),
PARTITION p5 VALUES LESS THAN (MAXVALUE)
);
'''
list = []
sqlBody = " PARTITION p{} VALUES LESS THAN ('{}')"
start, end = 2018, 2020
for y in range(start, end, 1):
for d in range(1, 13, 1):
dd = d
if dd < 10:
dd = '0{}'.format(d)
s = '{}-{}-01'.format(y, dd)
print(s)
# 注意分区名 p2018-01 p2018-02 被解释为同一分区名
''''''
# [Err] 1479 - Syntax error: RANGE PARTITIONING requires definition of VALUES LESS THAN for each partition
'''
innodb_version 5.6.16
protocol_version 10
slave_type_conversions
tls_version TLSv1,TLSv1.1,TLSv1.2
tokudb_version 7.5.6
version 5.6.16-log
version_comment Source distribution
version_compile_compiler GNU
version_compile_compiler_major 4
version_compile_compiler_minor 8
version_compile_machine x86_64
version_compile_os Linux
'''
sql = sqlBody.format(s.replace('-',''), s)
list.append(sql)
MAXVALUEsql = 'PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE)'
list.append(MAXVALUEsql)
sql = "ALTER TABLE target_tab PARTITION BY RANGE COLUMNS (target_filed) ({});".format(','.join(list))
# print(sql)
sql = "ALTER TABLE target_tab PARTITION BY RANGE COLUMNS (date) ({});".format(','.join(list))
print(sql)
上一篇:Apache HttpServer Installing the apache2.2 service 拒绝访问. :Failed to open the WinNT service manager


下一篇:Mybatis+Thymeleaf开发遇到的几个问题笔录