• python之MySQL MySQLdb 推荐使用姿势,解决中文乱码


    0.目录

    2.setup
    (1) 安装步骤,可以顺带安装mysql administrator和mysql query browser
    (2) 安装完毕,修改 my.ini
    (3) 重启 mysql 服务:
    (4) cmd 确认可用:
    (5) 安装 MySQLdb
    (6) 脚本内部会先对 execute 的unicode query 参数进行编码,可以手动加一句 print repr(query) 方便调试,懒得打monkey_patch的话。。。


    3.推荐使用姿势
    (1)连接:
    (2)创建数据库:不可省略COLLATE 'utf8_general_ci',有(2)则(3)可以省略 DEFAULT CHARSET=utf8
    (3)创建表


    4.中文乱码或其他异常
    (1)检查配置
    (2)在Windows cmd 插入或查询中文出现乱码
    (3)更改数据库编码
    (4)更改表编码

    1.参考

    有关编码,中文字符的官方解答,必看!!!

    A.11 MySQL 5.7 FAQ: MySQL Chinese, Japanese, and Korean Character Sets

    mysql> status;
    --------------
    mysql  Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using  EditLine wrapper
    
    Connection id:          21
    Current database:
    Current user:           root@localhost
    SSL:                    Not in use
    Current pager:          stdout
    Using outfile:          ''
    Using delimiter:        ;
    Server version:         5.7.20 MySQL Community Server (GPL)
    Protocol version:       10
    Connection:             Localhost via UNIX socket
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    utf8
    Conn.  characterset:    utf8
    UNIX socket:            /var/lib/mysql/mysql.sock
    Uptime:                 4 days 23 hours 32 min 54 sec
    
    Threads: 1  Questions: 186  Slow queries: 0  Opens: 141  Flush tables: 1  Open tables: 130  Queries per second avg: 0.000
    --------------

    2.setup

    (1)安装步骤: Windows 7系统安装MySQL5.5.21图解 ,可以顺带安装mysql administrator和mysql query browser

    14、在打开的字符编码的页面中,设置mysql要使用的字符编码,第一个是西文编码,第二个是多字节的通用utf8编码,第三个是手动,我们选择utf-8,如果在这里没有选择UTF-8这个编码的化,在使用JDBC连接数据库的时候,便会出现乱码,到那时需要加上如下的代码“useUnicode=true&characterEncoding=UTF-8 ”,才可以解决乱码问题,为了以后的方便所以设置为UTF-8编码,但是有一个问题,那就是当用哪个控制台插入汉字的时候,会报错,查询带汉字的表时,无法显示汉字,所在需要在每次进入MySQL后,设置一个系统参数“set names  gbk”,即可解决该问题,接下来单击“next”,继续配置,如图所示:

    (2) 安装完毕,修改 my.ini

    客户端cmd打开mysql,执行插入中文报错或插入中文乱码解决方案

    D:Program FilesMySQLMySQL Server 5.5

    原来是latin1,修改如下:

    # CLIENT SECTION  #will be read by MySQL client applications.
    default-character-set=utf8
    # SERVER SECTION    #will be used when a new schema or table
    character-set-server=utf8

    根据下文的 show variables like 'char%'; 显示:

    CLIENT SECTION对应如下三项,可以被连接参数临时覆盖 --default-character-set=utf8
    character_set_client     | latin1
    character_set_connection | latin1
    character_set_results    | latin1

    SERVER SECTION对应如下二项:
    character_set_database   | latin1   
    character_set_server     | latin1

    (3) 重启 mysql 服务:

    C:Userswin7>net stop mysql
    C:Userswin7>net start mysql

    (4) cmd 确认可用:

    mysql -u root -p
    回车然后输入密码

    (5) 安装 MySQLdb

    https://pypi.python.org/pypi/MySQL-python/1.2.5

    http://www.lfd.uci.edu/~gohlke/pythonlibs/#mysql-python

    (6) 脚本内部会先对 execute 的unicode query 参数进行编码,可以手动加一句 print repr(query) 方便调试,懒得打monkey_patch的话。。。

    C:Program FilesAnaconda2Libsite-packagesMySQLdbcursors.py

    class BaseCursor(object):
    
        def execute(self, query, args=None):
        
        
            if isinstance(query, unicode):
                query = query.encode(db.unicode_literal.charset)
            if args is not None:
                if isinstance(args, dict):
                    query = query % dict((key, db.literal(item))
                                         for key, item in args.iteritems())
                else:
                    query = query % tuple([db.literal(item) for item in args])
            print repr(query)  #手动加一句方便调试

    3.推荐使用姿势

    (1)连接:

    Python调用MySQLdb插入中文乱码的问题

    conn = MySQLdb.connect(host='localhost', user='root',passwd='123', db='account', charset='utf8')  # OK

    Python使用MySQL数据库(新)

    如果连接数据库用localhost连接不上,可以换127.0.0.1

     

    mysql -h localhost -u root -p  --default-character-set=utf8

    import MySQLdb
    conn= MySQLdb.connect(
        #host='localhost',    host
    ='127.0.0.1', port = 3306, user='root', passwd='root', #db ='database_name', charset='utf8' )
    curs = conn.cursor()

    Mysql中文乱码问题完美解决方案

    (2)创建数据库:不可省略COLLATE 'utf8_general_ci',有(2)则(3)可以省略 DEFAULT CHARSET=utf8

    curs.execute("""
    CREATE DATABASE database_name
    CHARACTER SET 'utf8'       
    COLLATE 'utf8_general_ci';
    """)

    #确认:
    curs.execute('SHOW DATABASES')
    curs.fetchall()

    (3)创建表

    curs.execute('USE database_name')

    curs.execute(""" CREATE TABLE table_name( id int not null auto_increment, name char(50) null, cate char(50) null, date datetime null, primary key(id) )engine=InnoDB DEFAULT CHARSET=utf8; """)

    #确认:
    curs.execute('SHOW TABLES')

    4.中文乱码或其他异常

    (1)检查配置

    mysql> show variables like 'char%';
    +--------------------------+---------------------------------------------------------+
    | Variable_name            | Value                                                   |
    +--------------------------+---------------------------------------------------------+
    | character_set_client     | utf8                                                    |
    | character_set_connection | utf8                                                    |
    | character_set_database   | utf8                                                    |
    | character_set_filesystem | binary                                                  |
    | character_set_results    | utf8                                                    |
    | character_set_server     | utf8                                                    |
    | character_set_system     | utf8                                                    |
    | character_sets_dir       | D:Program FilesMySQLMySQL Server 5.5sharecharsets |
    +--------------------------+---------------------------------------------------------+
    8 rows in set (0.00 sec)
    
    mysql> s
    --------------
    mysql  Ver 14.14 Distrib 5.5.56, for Win64 (AMD64)
    
    Connection id:          743
    Current database:
    Current user:           root@localhost
    SSL:                    Not in use
    Using delimiter:        ;
    Server version:         5.5.56 MySQL Community Server (GPL)
    Protocol version:       10
    Connection:             localhost via TCP/IP
    Server characterset:    utf8
    Db     characterset:    utf8
    Client characterset:    utf8
    Conn.  characterset:    utf8
    TCP port:               3306
    Uptime:                 1 day 4 min 47 sec
    
    Threads: 4  Questions: 7373195  Slow queries: 1323  Opens: 4725  Flush tables: 1  Open tables: 0  Qu
    eries per second avg: 85.055
    --------------
    
    mysql> use temp
    Database changed
    mysql> show create database temp;
    +----------+---------------------------------------------------------------+
    | Database | Create Database                                               |
    +----------+---------------------------------------------------------------+
    | temp     | CREATE DATABASE `temp` /*!40100 DEFAULT CHARACTER SET utf8 */ |
    +----------+---------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show full columns from test;
    +-------+----------+-----------------+------+-----+---------+----------------+----------------------
    -----------+---------+
    | Field | Type     | Collation       | Null | Key | Default | Extra          | Privileges
               | Comment |
    +-------+----------+-----------------+------+-----+---------+----------------+----------------------
    -----------+---------+
    | id    | int(11)  | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,
    references |         |
    | name  | char(50) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,
    references |         |
    | cate  | char(50) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,
    references |         |
    | date  | datetime | NULL            | YES  |     | NULL    |                | select,insert,update,
    references |         |
    +-------+----------+-----------------+------+-----+---------+----------------+----------------------
    -----------+---------+
    4 rows in set (0.19 sec)

    mysql> show variables like 'char%';

    除了character_set_filesystem | binary,一般要求其他项都是 utf8,通过按照前文的 2.setup 修改 my.ini 可以永久生效。

    mysql> set names utf8; 

    一次性修改 character_set_client, character_set_connection, and character_set_results

    mysql> help set names
    Name: 'SET NAMES'
    Description:
    Syntax:
    SET NAMES {'charset_name'
        [COLLATE 'collation_name'] | DEFAULT}
    
    This statement sets the three session system variables
    character_set_client, character_set_connection, and
    character_set_results to the given character set. Setting
    character_set_connection to charset_name also sets collation_connection
    to the default collation for charset_name. See
    http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html.
    
    The optional COLLATE clause may be used to specify a collation
    explicitly. If given, the collation must one of the permitted
    collations for charset_name.
    
    charset_name and collation_name may be quoted or unquoted.
    
    The default mapping can be restored by using a value of DEFAULT. The
    default depends on the server configuration.
    
    ucs2, utf16, and utf32 cannot be used as a client character set, which
    means that they do not work for SET NAMES.
    
    URL: http://dev.mysql.com/doc/refman/5.5/en/set-names.html

    (2)在Windows cmd 插入或查询中文出现乱码

    mysql insert中文乱码无法插入ERROR 1366 (HY000): Incorrect string value: 'xB1

    需要每次连接后输入如下:

    set character_set_client = gbk;
    set character_set_results = gbk;

    (3)更改数据库编码

    ALTER DATABASE db_name CHARACTER SET [=] charset_name

    ALTER DATABASE db_name COLLATE [=] collation_name

    仅对当前连接有效,实际意义???:set character_set_database = utf8;

    (4)更改表编码

    MySQL 中文显示乱码  看评论!

    ALTER TABLE tbl_name  CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

  • 相关阅读:
    Harbor
    Ansible自动化部署详细教程
    企业级-Shell案例18——目录入侵检测与告警
    企业级-Shell案例17——DOS攻击防范(自动屏蔽攻击IP)
    企业级-Shell案例16——自动发布PHP项目
    springboot启动报错,Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
    IO流05--毕向东JAVA基础教程视频学习笔记
    IO流04--毕向东JAVA基础教程视频学习笔记
    IO流03--毕向东JAVA基础教程视频学习笔记
    IO流02--毕向东JAVA基础教程视频学习笔记
  • 原文地址:https://www.cnblogs.com/my8100/p/mysql.html
Copyright © 2020-2023  润新知