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

时间：2022-09-01 15:26:04
# 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)

相关文章