mysql 默认 编码_Mysql数据库默认编码

Mysql数据库默认编码

查看使用的编码

1. 使用status查看

mysql> status

--------------

mysql Ver 14.14 Distrib 5.6.44, for Linux (x86_64) using EditLine wrapper

Connection id: 22

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.6.44 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/run/mysqld/mysqld.sock

Uptime: 6 hours 31 min 31 sec

Threads: 1 Questions: 905 Slow queries: 1 Opens: 74 Flush tables: 1 Open tables: 67 Queries per second avg: 0.038

--------------

Server characterset: latin1

Db characterset: latin1 (数据库编码格式)

Client characterset: utf8 (客户端连接进来的编码格式)

Conn. characterset: utf8 (接入的编码格式)

2. 使用show variables

show variables like 'character'

mysql> show variables like 'character%';

+--------------------------+----------------------------+

| 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/ |

+--------------------------+----------------------------+

/etc/my.cnf文件中修改配置信息

使用默认的配置信息

本机访问自己的数据库:Server characterset: latin1

status

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

show variables like 'character%'

+--------------------------+----------------------------+

| 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/ |

+--------------------------+----------------------------+

创建一个数据库encodtest,查看其编码信息

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

mysql> show variables like 'character%';

+--------------------------+----------------------------+

| 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/ |

+--------------------------+----------------------------+

character_set_database是数据库的编码格式

创建一个test表查看其默认的编码格式

show create table test;

+-------+-------------------------------

| Table | Create Table |

+-------+-------------------------------

| test | CREATE TABLE `test` (

`id` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+-------------------------------

DEFAULT CHARSET=latin1默认的编码格式是latin1,与数据库编码格式相同

更改配置文件/etc/my.cnf

添加设置:

[client]

default-character-set = utf8

[mysqld]

character-set-server=utf8

collation-server=utf8_unicode_ci

本机进入数据库

查看编码格式:

status

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. characterset: utf8

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/ |

+--------------------------+----------------------------+

创建数据库查看其编码

与上面相同

创建表查看编码:

+-------+------------------------------------------------------

| Table | Create Table |

+-------+------------------------------------------------------

| test | CREATE TABLE `test` (

`id` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

+-------+-----------------------------------------------------

DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci默认的编码格式与配置相同