MySQL Schema Design (5) Use Python to manage character sets

When we mention character sets, some people may say that the whole world is clean no matter the sky is split or utf8 is used. However, some character sets require more CPU, more memory and disk space, and even affect index usage. This does not include broken garbled characters. Obviously, it is necessary for us to take some time to understand the MySQL character set.
# Three levels: DB, Table, Column mysql> create database d charset utf8; Query OK, 1 row affected (0.04 sec) mysql> create table d. t-> (str varchar (10) charset latin1)-> default charset = utf8; Query OK, 0 rows affected (0.05 sec)

(I) display character set

Mysql> desc sakila. actor; + ------------- + hour + ------ + ----- + ----------------- + hour + | Field | Type | Null | Key | Default | Extra | + ------------- + hour + ------ + ----- + ------------------- + hour + | actor_id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | first_name | varchar (45) | NO | NULL | last_name | varchar (45) | NO | MUL | NULL | last_update | timestamp | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | + ------------- + hour + ------ + ----- + ------------------- + hour + 4 rows in set (0.00 sec)

[root@DataHacker ~]# cat dbapi.py #!/usr/bin/env ipython #coding = utf-8 #Author: linwaterbin@gmail.com #Time: 2014-1-29 import MySQLdb as dbapi USER = 'root' PASSWD = 'oracle' HOST = '127.0.0.1' DB = 'sakila' conn = dbapi.connect(user=USER,passwd=PASSWD,host=HOST,db=DB)[root@DataHacker ~]# ./show_charset.py --version 1.0

[Root @ DataHacker ~] #. /Show_charset.py-h Usage: show_charset.py [options] <arg1> <arg2> [<arg3>...] options: -- version show program's version number and exit-h, -- help show this help message and exit-d DB_NAME Database name (leave blank is all Databases) -t T_NAME Table name (leave blank is all tabless)-c C_NAME Column name (leave blank is all columns)

[root@DataHacker ~]# ./show_charset.py -d sakila -t actor sakila.actor.first_name: utf8 utf8_general_ci sakila.actor.last_name: utf8 utf8_general_ci

Mysql> create table tt (str char (2) charset utf8); Query OK, 0 rows affected (0.20 sec) mysql> create table tt (str int (11) charset utf8 ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'charset utf8) 'At line 1 [root @ DataHacker ~] # Cat show_charset.py #! /Usr/bin/env python from optparse import OptionParser from dbapi import conn import MySQLdb # function 1: Input def parse_options () to the command line parameter (): parser = OptionParser (usage = "% prog [options] <arg1> <arg2> [<arg3>...] ", version = '1. 0',) parser. add_option ("-d", dest = "db_name", help = "Database name (leave blank is all Databases)") parser. add_option ("-t", dest = "t_name", help = "Table name (leave blank is all tabless)") parser. add_option ("-c", dest = "c_name", help = "Column name (leave blank is all columns)") return parser. parse_args () # main function implementation: display the character set def show_charsets (): query = "" select * from information_schema.columns where table_schema not in ('mysql', 'information _ scheme ') and character_set_name is not null "" # filter if options using the three if conditions. db_name: query + = "and table_schema = '% S'" % options. db_name if options. t_name: query + = "and table_name = '% S'" % options. t_name if options. c_name: query + = "and column_name = '% S'" % options. c_name # The default return value is a tuples. We use the attribute cursors. convert DictCursor to the dictionary cur = conn. cursor (MySQLdb. cursors. dictCursor) cur.exe cute (query) for record in cur. fetchall (): character_set_name = record ['character _ SET_NAME '] collation_name = record ['collation _ name'] print "% s. % s. % s: \ t % s "% (record ['table _ scheme'], record ['table _ name'], record ['column _ name'], character_set_name, collation_name) cur. close () # Use try-finally to close the database connection try: options, args = parse_options () show_charsets () finally: conn. close ()

(Ii) modify the character set of a column
[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 before modification. 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) # modify [root @ DataHacker ~] #. /Modify. py testdb. t. name gbk successfully executed: alter table testdb. t modify column name varchar (10) CHARSET gbk # after modification, 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 se C) [root @ DataHacker ~] # Cat modify. py #! /Usr/bin/env python import MySQLdb from dbapi import * from optparse import OptionParser # The option value is omitted here. You only need to enter 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 () # main program def modify_column (): cur = conn. cursor (MySQLdb. cursors. dictCursor) v_ SQL = "" select * from information_schem A. columns where table_schema = '% s' and table_name =' % s' and column_name = '% S' "" % (schema_name, table_name, column_name) cur.exe cute (v_ SQL) row = cur. fetchone () # When row is null, the program requests to check whether the column exists 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 "% (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: Drawing" 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 ()