Rumah > pangkalan data > tutorial mysql > MySQLSchema设计(五)用Python管理字符集_MySQL

MySQLSchema设计(五)用Python管理字符集_MySQL

PHP中文网
Lepaskan: 2016-05-27 14:12:20
asal
1357 orang telah melayarinya

pythonbitsCN.com 一提字符集,可能有人会说,不管天崩地裂,全用utf8,整个世界都清净了。但某些字符集是需要更多CPU、消费更多的内存和磁盘空间、甚至影响索引使用,这还不包括令人蛋碎的乱码。可见、我们还是有必要花点时间略懂下MySQL字符集。
# 囊括三个层级:

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)
Salin selepas log masuk
㈠ 显示字符集
Salin selepas log masuk
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)
Salin selepas log masuk
[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
Salin selepas log masuk
[root@DataHacker ~]# ./show_charset.py -h Usage: show_charset.py [options] 
<arg1> 
<arg2> 
[<arg3>...] Options: --version show program&#39;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)
Salin selepas log masuk
[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
Salin selepas log masuk
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 &#39;charset utf8)&#39; 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] 
<arg1> <arg2> [<arg3>...]",version=&#39;1.0&#39;,) 
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 (&#39;mysql&#39;,&#39;INFORMATION_SCHEMA&#39;) 
and character_set_name is not null""" 
#三个if条件实现过滤 if options.db_name: query += " and table_schema=&#39;%s&#39;" % 
options.db_name if options.t_name: query += " and table_name=&#39;%s&#39;" % 
options.t_name if options.c_name: query += " and column_name=&#39;%s&#39;" % options.c_name 
#默认返回值形式是元组,我们通过属性cursors.DictCursor转为字典 
cur = conn.cursor(MySQLdb.cursors.DictCursor) 
cur.execute(query) for record in cur.fetchall(): 
character_set_name = record[&#39;CHARACTER_SET_NAME&#39;] 
collation_name = record[&#39;COLLATION_NAME&#39;] 
print "%s.%s.%s:t%st%s" % (record[&#39;TABLE_SCHEMA&#39;],
record[&#39;TABLE_NAME&#39;],record[&#39;COLUMN_NAME&#39;],character_set_name,collation_name) 
cur.close() #采用try-finally形式关闭数据库连接 
try: options,args = parse_options() show_charsets() finally: conn.close()
Salin selepas log masuk

㈡ 修改列的字符集

[root@DataHacker ~]# ./modify.py -h Usage: modify.py schema_name.table_name.column_name 
new_charset_name [new_collate_name] Options: --version show program&#39;s version number 
and exit -h, --help show this help message and exit
Salin selepas log masuk
#修改前 mysql> show create table testdb.tG; 
*************************** 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.tG; 
*************************** 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=&#39;1.0&#39;,) return parser.parse_args() 
#主程序 def modify_column(): cur = conn.cursor(MySQLdb.cursors.DictCursor) v_sql = """
 select * from information_schema.columns where table_schema=&#39;%s&#39; and table_name=&#39;%s&#39; 
 and column_name=&#39;%s&#39;""" % (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[&#39;COLUMN_TYPE&#39;] 
 column_default = row[&#39;COLUMN_DEFAULT&#39;] is_nullable = (row[&#39;IS_NULLABLE&#39;] == &#39;YES&#39;) 
 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 &#39;%s&#39;" % 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()
Salin selepas log masuk

以上就是MySQLSchema设计(五)用Python管理字符集_MySQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!


sumber:php.cn
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Artikel terbaru oleh pengarang
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan