mysql数据库字符编码修改_mysql 修改数据库的字符编码

# 修改数据库的字符编码

mysql> alter database redmine default character set 'utf8';

Query OK, 1 row affected (0.08 sec)

# 显示数据库的字符编码

mysql> show variables like 'character_set_database';

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

| Variable_name | Value |

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

| character_set_database | utf8 |

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

1 row in set (0.00 sec)

# 查看表中字段的字符编码

mysql> show full columns from projects;

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

| Field | Type | Collation

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

| id | int(11) | NULL

| name | varchar(255) | utf8_general_ci

| description | text | utf8_general_ci

| homepage | varchar(255) | utf8_general_ci

| is_public | tinyint(1) | NULL

| parent_id | int(11) | NULL

| created_on | datetime | NULL

| updated_on | datetime | NULL

| identifier | varchar(255) | utf8_general_ci

| status | int(11) | NULL

| lft | int(11) | NULL

| rgt | int(11) | NULL

| inherit_members | tinyint(1) | NULL

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

# 修改表中字段的所有编码

mysql> alter table auth_sources convert to character set utf8;

# 修改数据库中的表的字符编码，复制出来批量，批量运行。

mysql> select concat ('alter table ', table_name, ' convert to character set utf8;') from information_schema.tables where table_schema='redmine';

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

| concat ('alter table ', table_name, ' convert to character set utf8;') |

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

| alter table attachments convert to character set utf8; |

| alter table auth_sources convert to character set utf8; |

| alter table boards convert to character set utf8; |

| alter table changes convert to character set utf8; |

| alter table changeset_parents convert to character set utf8; |

| alter table changesets convert to character set utf8; |

| alter table changesets_issues convert to character set utf8; |

| alter table comments convert to character set utf8; |

| alter table custom_fields convert to character set utf8; |

| alter table custom_fields_projects convert to character set utf8; |

| alter table custom_fields_roles convert to character set utf8; |

| alter table custom_fields_trackers convert to character set utf8; |

| alter table custom_values convert to character set utf8; |

| alter table documents convert to character set utf8; |

| alter table enabled_modules convert to character set utf8; |

| alter table enumerations convert to character set utf8; |

| alter table groups_users convert to character set utf8; |

| alter table issue_categories convert to character set utf8; |

| alter table issue_relations convert to character set utf8; |

| alter table issue_statuses convert to character set utf8; |

| alter table issues convert to character set utf8; |

| alter table journal_details convert to character set utf8; |

| alter table journals convert to character set utf8; |

| alter table member_roles convert to character set utf8; |

| alter table members convert to character set utf8; |

| alter table messages convert to character set utf8; |

| alter table news convert to character set utf8; |

| alter table open_id_authentication_associations convert to character set utf8; |

| alter table open_id_authentication_nonces convert to character set utf8; |

| alter table projects convert to character set utf8; |

| alter table projects_trackers convert to character set utf8; |

| alter table queries convert to character set utf8; |

| alter table queries_roles convert to character set utf8; |

| alter table repositories convert to character set utf8; |

| alter table roles convert to character set utf8; |

| alter table schema_migrations convert to character set utf8; |

| alter table settings convert to character set utf8; |

| alter table time_entries convert to character set utf8; |

| alter table tokens convert to character set utf8; |

| alter table trackers convert to character set utf8; |

| alter table user_preferences convert to character set utf8; |

| alter table users convert to character set utf8; |

| alter table versions convert to character set utf8; |

| alter table watchers convert to character set utf8; |

| alter table wiki_content_versions convert to character set utf8; |

| alter table wiki_contents convert to character set utf8; |

| alter table wiki_pages convert to character set utf8; |

| alter table wiki_redirects convert to character set utf8; |

| alter table wikis convert to character set utf8; |

| alter table workflows convert to character set utf8; |

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