# 囊括三个层级：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)
㈠ 显示字符集mysql> desc sakila.actor; +-------------+----------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+-------------------+-----------------------------+ | 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 | +-------------+----------------------+------+-----+-------------------+-----------------------------+ 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]
[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 # 函数一：命令行参数输入 def parse_options(): parser = OptionParser(usage="%prog [options][ ...]",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() # 主功能实现：显示字符集 def show_charsets(): query="""select * from information_schema.columns where table_schema not in ('mysql','INFORMATION_SCHEMA') and character_set_name is not null""" #三个if条件实现过滤 if options.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 #默认返回值形式是元组，我们通过属性cursors.DictCursor转为字典 cur = conn.cursor(MySQLdb.cursors.DictCursor) cur.execute(query) for record in cur.fetchall(): character_set_name = record['CHARACTER_SET_NAME'] collation_name = record['COLLATION_NAME'] print "%s.%s.%s:\t%s\t%s" % (record['TABLE_SCHEMA'],record['TABLE_NAME'],record['