mysql 修改字符集

2017年3月10日

查看数据库支持的所有字符集
SHOW CHARACTER SET;
SHOW COLLATION;

root@iZbp18uglnzeopl4c2xy3uZ:/# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.21-MariaDB-1~jessie mariadb.org binary distributionCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> show variables like 'character_set%%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

二.设置编码

命令行修改 临时修改只在当前生效重启后失效

1. SET NAMES 'utf8';

它相当于下面的三句指令：

  1. SET character_set_client = utf8;
  2. SET character_set_results = utf8;
  3. SET character_set_connection = utf8;
  4. 一般只有在访问之前执行这个代码就解决问题了 但是重启数据库后就没用了
  5. SET character_set_client = utf8;
  6. SET character_set_connection = utf8;
  7. SET character_set_database = utf8;
  8. SET character_set_results = utf8;
  9. SET character_set_server = utf8;

2. 创建数据库是指定编码

CREATE DATABASE database_name CHARACTER SET utf8;MariaDB [(none)]> CREATE DATABASE foo CHARACTER SET utf8;
 
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> USE foo;
 
Database changed
 
MariaDB [foo]> SHOW VARIABLES LIKE 'character%';
 
+--------------------------+----------------------------+
| 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 | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
 
8 rows in set (0.00 sec)
 
MariaDB [foo]> SHOW VARIABLES LIKE 'collation%';
 
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+

3 rows in set (0.00 sec)

注意：虽然在建库的时候有指定字符集，但character_set_server 仍然是latin1, 需要修改配置文件/etc/my.cnf

3. 创建表指定编码

CREATE TABLE `type` (
`id` int(10) unsigned NOT NULL auto_increment,
`flag_deleted` enum('Y','N') character set utf8 NOT NULL default 'N',
`flag_type` int(5) NOT NULL default '0',
`type_name` varchar(50) character set utf8 NOT NULL default '',
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;示例：
 
CREATE TABLE mytable
 
(
 
columnn1 INT,
 
columnn2 VARCHAR(10)
 
)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
 
CREATE OR REPLACE TABLE mytable
 
(columnn1 INT,
 
columnn2 VARCHAR(10),
 
column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
 
)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
 
SELECT * FROM customers ORDER BY lastname, firstname COLLATE latin1_general_cs;
还可以限制某个字段的字符集作为筛选条件

4. 修改数据库成utf8

  1. ALTER DATABASE database_name CHARACTER SET utf8;

5. 修改表默认用utf8

  1. ALTER TABLE table_name CHARACTER SET utf8;

6. 修改字段用utf8

MariaDB [foo]> DESC hello;+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | char(50) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
 
2 rows in set (0.00 sec)
 
MariaDB [foo]> ALTER TABLE hello MODIFY name char(50) CHARACTER SET utf8;
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0 Duplicates: 0 Warnings: 0

修改配置文件 一劳永逸的办法

Linux下一般是 /etc/my.cnf，Windows下一般在系统目录下或者在MySQL的安装目录下名字叫my.ini

Mysql 5.7
 
 
[client]
default-character-set = utf8
 
[mysql]
default-character-set=utf8
 
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
 
(root@localhost:mysqld.sock) [(none)]> 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 | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
 
MariaDB 10.*
[client]
default-character-set=utf8
***
[mysql]
default-character-set=utf8
***
[mysqld]
init_connect = 'SET collation_connection = utf8_general_ci'
init_connect = 'SET NAMES utf8'
character_set_server = utf8
collation_server = utf8_general_ci
 
 
MariaDB [(none)]> SHOW VARIABLES LIKE 'collation%';
 
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
 
3 rows in set (0.00 sec)
 
MariaDB [(none)]> SHOW VARIABLES LIKE 'character%';
 
+--------------------------+----------------------------+
| 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 | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
 
8 rows in set (0.00 sec)注意：新版本的mariadb,如mariadb-10.1的配置些许有些变化
 
标签：

没有评论

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注