java生成mysql分区语句

1.分区

之前介绍了分区，
http://qinyueming.cc/2016/09/26/mysql-partitioning/，可以很容易的写出range分区语句出来,但是最好别手写，手写容易出错。

2.程序

最新学习java，顺便用java写了一个自动生成zabbix分区的程序出来。

关键点在于用了StringBuilder. 参看 java编程思想 里面的UsingStringbuilder.java:

import java.util.Random;

public class UsingStringBuilder{
	public static Random rand = new Random(47);
	public String toString(){
		StringBuilder result = new StringBuilder("[");
		for(int i = 0; i < 25; i++){
			result.append(rand.nextInt(100));
			result.append(", ");
		}
		result.delete(result.length()-2, result.length());
		result.append("]");
		return result.toString();
	}
	public static void main(String[] args){
		UsingStringBuilder usb = new UsingStringBuilder();
		System.out.println(usb);
	}
}

另一个关键点是日期转化为unix timestamp。用到了SimpleDateFormat,SimpleDateFormat的parse方法可以直接解析返回一个Date对象，Date对象的getTime()返回的是unix timestamp的毫秒数，除以1000就是mysql里面的unix timestamp了。用起来还是非常爽的。总觉得比python的类库要好用。下面是一个小例子:

/**
 * Created by aca on 10/4/16.
 */
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DateTest {
    public static void main(String[] args) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String s1 = "2016-10-04";
        String s2 = String.format("2016-%02d-01", 11);
        System.out.println(s2);
        try{
            Date d = df.parse(s2);
            System.out.println(d.getTime() / 1000);

        }catch(ParseException pe){
            System.out.println("parse error.");
        }
    }
}

3.完整程序

见https://gist.github.com/gqdw/97ca758d3d0866baa039eb4fd02bbda8

/**
 * Created by aca on 10/4/16.
 */

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class Genpartition {
    // alter table xxx partition by range(id)
    // (partition p0 values less than (xx) ,
    // partition p1 values less than (yy));
    static String Gensql(String tablename, String column){
        StringBuilder result = new StringBuilder("alter table ");
//        String res = "alter table " + tablename + " partition by range(" + column + ")" ;
        result.append(tablename);
        result.append(" partition by range(");
        result.append(column);
        result.append(")(\n");http://qinyueming.cc/2016/09/26/mysql-partitioning/

        for (int i = 1; i <= 12; i++) {
            SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
            String txtdate = String.format("2016-%02d-01", i);
            //= "2016-%d-01".format(i);
//            System.out.println(txtdate);
            Date date = new Date();
            try {
                date = dateformat.parse(txtdate);

            }catch(ParseException pe){
                System.out.println("parse error.");
            }
            result.append("partition p" );
            result.append(String.format("2016%02d", i));
            result.append(" values less than (");
            result.append(date.getTime() / 1000);
            result.append("),\n");
        }
        result.delete(result.length()-2, result.length());
        result.append(");\n");
        return result.toString();
    }
    public static void main(String[] args) {
        String[] tables = {"history_uint", "trends_uint"};
        for(String t:tables)
            System.out.println(Gensql(t, "clock"));
    }
}

4.生成结果

alter table history_uint partition by range(clock)(
partition p201601 values less than (1451577600),
partition p201602 values less than (1454256000),
partition p201603 values less than (1456761600),
partition p201604 values less than (1459440000),
partition p201605 values less than (1462032000),
partition p201606 values less than (1464710400),
partition p201607 values less than (1467302400),
partition p201608 values less than (1469980800),
partition p201609 values less than (1472659200),
partition p201610 values less than (1475251200),
partition p201611 values less than (1477929600),
partition p201612 values less than (1480521600));

alter table trends_uint partition by range(clock)(
partition p201601 values less than (1451577600),
partition p201602 values less than (1454256000),
partition p201603 values less than (1456761600),
partition p201604 values less than (1459440000),
partition p201605 values less than (1462032000),
partition p201606 values less than (1464710400),
partition p201607 values less than (1467302400),
partition p201608 values less than (1469980800),
partition p201609 values less than (1472659200),
partition p201610 values less than (1475251200),
partition p201611 values less than (1477929600),
partition p201612 values less than (1480521600));

可以直接在mysql里面跑。