データベース復旧エラーの原因となる MySQL 文字セットの簡単な分析

MySQL の文字セットエンコーディングが正しくないデータをインポートすると、エラーが表示されます。これは、データを挿入する場合と同じです。保存されたデータが MySQL エンコーディングと異なる場合、文字化けした文字をインポートしたり、挿入データが失われたりといった問題が必ず発生します。例を見てみましょう。

データベースのリカバリエラー: 文字セットの問題により、元のデータベースのデフォルトのエンコーディングは latin1 ですが、新しくバックアップされたデータベースのエンコーディングは utf8 であるため、リカバリエラーが発生します。

[root@hk byrd]# /usr/local/mysql/bin/mysql -uroot -p'admin' t4x < /tmp/11x-B-2014-06-18.sql 
292 行目のエラー 1064 (42000): SQL 構文にエラーがあります。MySQL サーバーのバージョンに対応するマニュアルを参照して、''[caption id=\"attachment_271\" align=\"aligncenter\" width=\"300\"]<a href=\"ht' 1 行目付近の正しい構文を確認してください。

修復方法（未テスト）:

[root@Test ~]# /usr/local/mysql/bin/mysql -uroot -p'admin' --default-character-set=latin1 t4x < /tmp/11x-B-2014-06-18.sql 
マイグレーション
-- MySQL ダンプ 10.13 Distrib 5.5.37、Linux (x86_64) 用
--
-- ホスト: localhost データベース: t4x
-- ------------------------------------------------------
--サーバーバージョン 5.5.37-ログ
/*!40101 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT を設定します */;
/*!40101 @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS を設定します */;
/*!40101 @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION を設定します */;
/*!40101 名前をutf8に設定 */;
/*!40103 @OLD_TIME_ZONE=@@TIME_ZONE を設定します */;
/*!40103 TIME_ZONE=' 00:00' に設定 */;
/*!40014 @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS、UNIQUE_CHECKS=0 に設定 */;
/*!40014 @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS、FOREIGN_KEY_CHECKS=0 に設定 */;
/*!40101 @OLD_SQL_MODE=@@SQL_MODE、SQL_MODE='NO_AUTO_VALUE_ON_ZERO' を設定します */;
/*!40111 @OLD_SQL_NOTES=@@SQL_NOTES、SQL_NOTES=0 に設定 */;
--
-- 現在のデータベース: `t4x`
--
CREATE DATABASE /*!32312 存在しない場合は*/ `t4x` /*!40100 デフォルトの文字セット utf8 */;
--
-- テーブル `wp_baidusubmit_sitemap` のテーブル構造
--
`wp_baidusubmit_sitemap` が存在する場合はテーブルを削除します。
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
テーブル `wp_baidusubmit_sitemap` を作成します (
 `sid` int(11) NOT NULL AUTO_INCREMENT,
 `url` varchar(255) NOT NULL デフォルト ''
 `type` tinyint(4) NOT NULL、
 `create_time` int(10) NOT NULL デフォルト '0',
 `start` int(11) デフォルト '0',
 `end` int(11) デフォルト '0',
 `item_count` int(10) 符号なし デフォルト '0',
 `file_size` int(10) 符号なし デフォルト '0',
 `lost_time` int(10) 符号なし デフォルト '0',
 主キー (`sid`)、
 キー `start` (`start`)、
 キー `end` (`end`)
) ENGINE=MyISAM AUTO_INCREMENT=84 デフォルトCHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
0
1
[root@hk byrd]# /usr/local/mysql/bin/mysql -uroot -p'admin' t4x < /tmp/t4x-B-2014-06-17.sql 
295 行目のエラー 1064 (42000): SQL 構文にエラーがあります。1 行目の ''i' 付近で使用する正しい構文については、MySQL サーバーのバージョンに対応するマニュアルを確認してください。

マイグレーション

--MySQL ダンプ 10.11
--
-- ホスト: localhost データベース: t4x
-- ------------------------------------------------------
--サーバーバージョン 5.0.95-ログ
/*!40101 @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT を設定します */;
/*!40101 @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS を設定します */;
/*!40101 @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION を設定します */;
/*!40101 名前をutf8に設定 */;
/*!40103 @OLD_TIME_ZONE=@@TIME_ZONE を設定します */;
/*!40103 TIME_ZONE=' 00:00' に設定 */;
/*!40014 @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS、UNIQUE_CHECKS=0 に設定 */;
/*!40014 @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS、FOREIGN_KEY_CHECKS=0 に設定 */;
/*!40101 @OLD_SQL_MODE=@@SQL_MODE、SQL_MODE='NO_AUTO_VALUE_ON_ZERO' を設定します */;
/*!40111 @OLD_SQL_NOTES=@@SQL_NOTES、SQL_NOTES=0 に設定 */;
-- 
-- 現在のデータベース: `t4x`
--
CREATE DATABASE /*!32312 存在しない場合は*/ `t4x` /*!40100 デフォルト文字セット latin1 */;
`t4x` を使用します。
--
-- テーブル `wp_baidusubmit_sitemap` のテーブル構造
--
`wp_baidusubmit_sitemap` が存在する場合はテーブルを削除します。
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
テーブル `wp_baidusubmit_sitemap` を作成します (
 `sid` int(11) NOT NULL auto_increment,
 `url` varchar(255) NOT NULL デフォルト ''
 `type` tinyint(4) NOT NULL、
 `create_time` int(10) NOT NULL デフォルト '0',
 `start` int(11) デフォルト '0',
 `end` int(11) デフォルト '0',
 `item_count` int(10) 符号なし デフォルト '0',
 `file_size` int(10) 符号なし デフォルト '0',
 `lost_time` int(10) 符号なし デフォルト '0',
 主キー (`sid`)、
 キー `start` (`start`)、
 キー `end` (`end`)
) ENGINE=MyISAM AUTO_INCREMENT=83 デフォルトCHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

文字セット関連:

マイグレーション

mysql> '%character_set%' のような変数を表示します。
-------------------------- ---------------------------- 
| 変数名 | 値 |
-------------------------- ---------------------------- 
| 文字セットクライアント | utf8 |
| 文字セット接続 | utf8 |
| 文字セットデータベース | utf8 |
| 文字セットファイルシステム | バイナリ |
| 文字セット結果 | utf8 |
| 文字セットサーバー | latin1 |
| 文字セットシステム | utf8 |
| 文字セットディレクトリ | /usr/share/mysql/charsets/ |
-------------------------- ---------------------------- 
mysql>名前をgbkに設定します。
mysql> '%character_set%' のような変数を表示します。
-------------------------- ---------------------------- 
| 変数名 | 値 |
-------------------------- ---------------------------- 
| 文字セットクライアント | gbk |
| 文字セット接続 | gbk |
| 文字セットデータベース | utf8 |
| 文字セットファイルシステム | バイナリ |
| 文字セット結果 | gbk |
| 文字セットサーバー | latin1 |
| 文字セットシステム | utf8 |
| 文字セットディレクトリ | /usr/share/mysql/charsets/ |
-------------------------- ---------------------------- 
mysql>system cat /etc/my.cnf | grep default #クライアントは、以下の文字セットクライアントを設定します default-character-set=gbk
mysql> '%character_set%' のような変数を表示します。
-------------------------- ---------------------------- 
| 変数名 | 値 |
-------------------------- ---------------------------- 
| 文字セットクライアント | gbk |
| 文字セット接続 | gbk |
| 文字セットデータベース | latin1 |
| 文字セットファイルシステム | バイナリ |
| 文字セット結果 | gbk |
| 文字セットサーバー | latin1 |
| 文字セットシステム | utf8 |
| 文字セットディレクトリ | /usr/share/mysql/charsets/ |
-------------------------- ---------------------------- 
mysql> system cat /etc/my.cnf|grep character-set-server #クライアントはmysqldの以下の文字セットを設定します character-set-server = cp1250 
mysql> '%character_set%' のような変数を表示します。
-------------------------- -------------------------------------------- 
| 変数名 | 値 |
-------------------------- -------------------------------------------- 
| 文字セットクライアント | utf8 |
| 文字セット接続 | utf8 |
| 文字セットデータベース | cp1250 |
| 文字セットファイルシステム | バイナリ |
| 文字セット結果 | utf8 |
| 文字セットサーバー | cp1250 |
| 文字セットシステム | utf8 |
| 文字セットディレクトリ | /byrd/service/mysql/5.6.26/share/charsets/ |
-------------------------- -------------------------------------------- 
セット内の行数は 8 です (0.00 秒)

その他の設定方法:

データベースの文字セットを変更する

  mysql>mydbを使用する
  mysql>データベースmydbの文字セットをutf-8に変更します。

データベースを作成し、データベースの文字セットを指定します

  mysql>データベースmydbの文字セットutf-8を作成します。

設定ファイルを通じて変更します:

/var/lib/mysql/mydb/db.opt を変更する

デフォルト文字セット=latin1
デフォルト照合順序=latin1_swedish_ci

のために

デフォルトの文字セット=utf8
デフォルト照合=utf8_general_ci

MySQLを再起動します。

[root@bogon ~]# /etc/rc.d/init.d/mysqlを再起動します

MySQL コマンドラインから変更します。

mysql> character_set_client=utf8 を設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> character_set_connection=utf8 を設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> character_set_database=utf8 を設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> character_set_results=utf8 を設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> character_set_server=utf8 を設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> 文字セットシステムを utf8 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
mysql> collation_connection=utf8 を設定します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
mysql> collation_database=utf8 を設定します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
mysql> collation_server=utf8 を設定します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

チェック：

mysql> 'character_set_%' のような変数を表示します。
-------------------------- ---------------------------- 
| 変数名 | 値 |
-------------------------- ---------------------------- 
| 文字セットクライアント | utf8 |
| 文字セット接続 | utf8 |
| 文字セットデータベース | utf8 |
| 文字セットファイルシステム | バイナリ |
| 文字セット結果 | utf8 |
| 文字セットサーバー | utf8 |
| 文字セットシステム | utf8 |
| 文字セットディレクトリ | /usr/share/mysql/charsets/ |
--------------------------------------------- 
セット内の 8 行 (0.03 秒)
mysql> 'collation_%' のような変数を表示します。
---------------------- ----------------- 
| 変数名 | 値 |
---------------------- ----------------- 
| 照合接続 | utf8_general_ci |
| 照合データベース | utf8_general_ci |
| 照合サーバー | utf8_general_ci |
---------------------- ----------------- 
3 行セット (0.04 秒)

要約する

以上が、データベース回復エラーの原因となる MySQL 文字セットの問題を簡単に分析したこの記事の内容のすべてです。皆様のお役に立てれば幸いです。ご質問がございましたら、いつでもメッセージを残してください。編集者がすぐに返信いたします。このサイトをサポートしてくれた友人たちに感謝します!

以下もご興味があるかもしれません: