[root@DataHacker ~]# ./modify.py -h Usage: modify.py schema_name.table_name.column_name new_charset_name [new_collate_name] Options: --version show program's version number and exit -h, --help show this help message and exit
#修改前 mysql> show create table testdb.t\G; *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL, `name` varchar(10) CHARACTER SET latin1 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) #修改 [root@DataHacker ~]# ./modify.py testdb.t.name gbk successfully executed: alter table testdb.t modify column name varchar(10) CHARSET gbk #修改后 mysql> show create table testdb.t\G; *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL, `name` varchar(10) CHARACTER SET gbk DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec) [root@DataHacker ~]# cat modify.py #!/usr/bin/env python import MySQLdb from dbapi import * from optparse import OptionParser #这里省略掉option值，只要求输入args def parse_options(): parser = OptionParser(usage="\n%prog schema_name.table_name.column_name new_charset_name [new_collate_name]",version='1.0',) return parser.parse_args() #主程序 def modify_column(): cur = conn.cursor(MySQLdb.cursors.DictCursor) v_sql = """ select * from information_schema.columns where table_schema='%s' and table_name='%s' and column_name='%s'""" % (schema_name,table_name,column_name) cur.execute(v_sql) row = cur.fetchone() #当row为null时，程序请求检查column是否存在 if not row: print "please check schema_name.table_name.column_name whether exists " exit(1) column_type = row['COLUMN_TYPE'] column_default = row['COLUMN_DEFAULT'] is_nullable = (row['IS_NULLABLE'] == 'YES') query = "alter table %s.%s modify column %s %s" % (schema_name,table_name,column_name,column_type) query += " CHARSET %s" % new_charset if collation_supplied: query += "COLLATE %s" % new_collation if not is_nullable: query += "NOT NULL" if column_default: query += "DEFAULT '%s'" % column_default try: alter_cur = conn.cursor() alter_cur.execute(query) print "successfully executed:\n \t%s" % query finally: alter_cur.close() cur.close() try: (options,args) = parse_options() if not 2<= len(args) <=3: print "Usage: schema_name.table_name.column_name new_charset_name [new_collate_name]" exit(1) column_tokens = args[0].split(".") if len(column_tokens) != 3: print "column must in the following format: schema_name.table_name.column_name" exit(1) schema_name,table_name,column_name = column_tokens new_charset = args[1] collation_supplied = (len(args) == 3) if collation_supplied: new_collation = args[2] modify_column() finally: if conn: conn.close()