mysql 5.0 utf8mb4_mysql 5.5 数据库 utf8改utf8mb4

由于需要用到utf8mb4，之前是utf8现在给改成utf8mb4

查看当前环境

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

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

| Variable_name | Value |

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

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| collation_connection | latin1_swedish_ci |

| collation_database | utf8mb4_general_ci |

| collation_server | utf8mb4_general_ci |

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

打开MySQL的配置文件，将字符集的配置修改成如下：

[client]

default-character-set = utf8mb4

[mysql]

default-character-set = utf8mb4

[mysqld]

character-set-client-handshake = FALSE

character-set-server = utf8mb4

collation-server = utf8mb4_general_ci

init_connect='SET NAMES utf8mb4'

修改后

root@localhost:(none)>SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

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

| Variable_name | Value |

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

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| collation_connection | utf8mb4_unicode_ci |

| collation_database | utf8mb4_unicode_ci |

| collation_server | utf8mb4_unicode_ci |

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

root@localhost:(none)>\s;

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

mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:        61

Current database:

Current user:        root@localhost

SSL:            Not in use

Current pager:        stdout

Using outfile:        ''

Using delimiter:    ;

Server version:        5.5.36-log Source distribution

Protocol version:    10

Connection:        Localhost via UNIX socket

Server characterset:    utf8mb4

Db     characterset:    utf8mb4

Client characterset:    utf8mb4

Conn.  characterset:    utf8mb4

UNIX socket:        /tmp/mysql.sock

Uptime:            3 min 22 sec

修改后发现连接报错

mysql: Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file

修改/usr/share/mysql/charsets/Index.xml

修改前

Unicode

UTF-8 Unicode

utf-8

primary

compiled

binary

compiled

修改后

Unicode

UTF-8 Unicode

utf-8

primary

compiled

binary

compiled