Mysql编码——查询和设置

「爱情、让人受尽委屈。」 2021-11-17 17:18 301阅读 0赞

## 1、查看mysql编码 ##

## 2、临时修改编码(重启后失效) ##

mysql> alter database name character set utf8;
    mysql> set character_set_connection=utf8;
    Query OK, 0 rows affected (0.00 sec)
    mysql> set character_set_connection=utf8;
    Query OK, 0 rows affected (0.00 sec)
    mysql> set character_set_results=utf8;
    Query OK, 0 rows affected (0.00 sec)
    mysql> set character_set_server=utf8;
    Query OK, 0 rows affected (0.00 sec)

## 3、永久设置编码(修改配置文件,重启Mysqld服务) ##

$ vi /etc/my.cnf
    [mysqld]
    default-character-set=utf8
    character_set_server=utf8
    init_connect='SET NAMES utf8'
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    [mysql]
    default-character-set=utf8
    [musql.server]
    default-character-set=utf8
    [client]
    default-character-set=utf8
    [mysqld_safe]
    default-character-set=utf8
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

Windows系统上有所不同，mysql目录下my.ini：

[client]
    port=3306
    default-character-set=utf8
    #客户端字符类型，与服务端一致就行，建议utf8
    [mysqld]
    port=3306
    character_set_server=utf8
    #服务端字符类型，建议utf8
    basedir=D:/MySql/mysql-5.6.17-winx64
    #解压根目录
    datadir=D:/MySql/mysql-5.6.17-winx64/data
    #解压根目录/data
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    [WinMySQLAdmin]

## 4、创建MySql数据库和表并设置编码 ##

CREATE DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    
    CREATE TABLE IF NOT EXISTS test_tb( id int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    或
    CREATE TABLE IF NOT EXISTS test_tb( id int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL);
    -- 修改表编码
    ALTER TABLE  `test_tb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    --修改列编码
    ALTER TABLE  `test_tb` CHANGE  `name`  `name` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;