MySQLのインストール後にデータベースにつなぐまで
やりたいことによって異なるが、下記流れを確認する。
１．MySQLの稼働確認
２．MySQLへの接続
３．データベースの確認
４．データベースへの接続
５．テーブルの確認
６．SQLの発行
７．接続の終了
１．MySQLの稼働確認
scコマンドで起動状態を確認する。
C:\>sc query MySQL
SERVICE_NAME: MySQL
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0
状態だけを表示させたい場合は、次のようにすればよい。
C:\>sc query mysql | findstr -i state
STATE : 4 RUNNING
２．MySQLへの接続
C:\>mysql -uroot -p Enter password: ******* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.5.36 MySQL Community Server (GPL) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
３．データベースの確認
インストールしたばかりでは、下記4つのデータベースが作成済みのようだ。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
４．データベースへの接続および接続確認
データベースへの接続
mysql> use test; Database changed
データベースへの接続確認。
mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec)
５．テーブルの確認
mysql> show tables; Empty set (0.00 sec)
testデータベースには、テーブルが無い。
６．SQLの発行
テーブルを作成する。
mysql> create table sampleTBL(id char(100)); Query OK, 0 rows affected (0.18 sec)
テーブルが作成されたことを確認をする。
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | sampletbl | +----------------+ 1 row in set (0.00 sec)
作成したテーブルの構造を確認する（describeを使用する場合）。
mysql> describe sampletbl; +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | char(100) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ 1 row in set (0.13 sec)
show fieldsを使用しても可能。
mysql> show fields from sampletbl; +-------+-----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+---------+-------+ | id | char(100) | YES | | NULL | | +-------+-----------+------+-----+---------+-------+ 1 row in set (0.13 sec)
show create tableを実行しても同様の内容を確認できる。
mysql> show create table sampletbl; +-----------+------------------------------------------------------------------- ----------------------------+ | Table | Create Table | +-----------+------------------------------------------------------------------- ----------------------------+ | sampletbl | CREATE TABLE `sampletbl` ( `id` char(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-----------+------------------------------------------------------------------- ----------------------------+ 1 row in set (0.00 sec)
データの投入。
mysql> insert into sampletbl(id) values('1'); Query OK, 1 row affected (0.03 sec)
データの確認。
mysql> select * from sampletbl; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec)
７．接続の終了
mysql> quit; Bye
【SQLiteからMySQLへの移行１】 SQLiteでのDBのダンプ
SQLiteでは、charsetがShift-JISでは扱えないため、MySQLの使用も検討し始めた。
プログラム側でcharsetを変換するプログラムを作成する方法も検討したが、C言語でiconvを使用したcharset変換DLLの作成を試みたが、DLL作成時にエラーとなり、それが解決出来なかったので、DB側で解決することを考え始めた。。。
その第一歩として、SQLiteのデータをダンプする。
なお、DBのサンプルは、@bighope さんの「MT4からＤＡＴＡＢＡＳＥを利用する。」で紹介されているEventDate.dbを利用させて頂きました。
１．SQLiteでDBに接続する
接続し、接続先データベースの確認、テーブルの確認を行う。
C:\dev\SQLite>sqlite3.exe EventDate.db SQLite version 3.8.2 2013-12-06 14:53:30 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .databases seq name file --- --------------- ---------------------------------------------------- 0 main C:\dev\SQLite\EventDate.db sqlite> .tables HistoricalDate sqlite>
２．コマンド実行結果の出力先をダンプファイルに変更する。
SQLiteのダンプとは、「データだけでなくテーブルの構造なども含むデータベースに関する情報をSQL文の形式で出力すること」なので、出力先の拡張子はsqlとする。
実行するとファイルが作成され、コマンドの実行結果が全てそこに出力される。
※）コマンドの実行結果が全て出力されるので、ダンプコマンド以外のコマンドを入力すると余計なものがダンプファイルに出力されるので注意すること。
sqlite> .output EventDateDump.sql
３．ダンプを実効する
sqlite> .dump HistoricalDate
４．出力先を標準出力に戻す
ダンプには直接関係ないが、ダンプ後にコマンドを入力してダンプファイルを壊さないようにするために、コマンド実行結果の出力先を標準出力に戻しておく。
sqlite> .output stdout
５．ダンプファイルを確認する
確かに、SQLが出力されている。
外部キーの設定、トランザクションの開始、テーブル作成、データ投入、そして最後にコミットとなっている。
create table時の、テーブル名とカラム名が（[]）で囲まれているなど、そのままMySQLでは実行できない点がありそうなので、そこはMySQLで実行時に考える。
PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE [HistoricalDate] ([id] INTEGER NOT NULL UNIQUE, [nen] INTEGER, [tuki] INTEGER, [niti] INTEGER, [zi] INTEGER, [fun] INTEGER, [kuni] TEXT, [event] TEXT, [lank] INTEGER, [mae] TEXT, [yoso] TEXT, [keka] TEXT, PRIMARY KEY(id) ); INSERT INTO "HistoricalDate" VALUES(1,2007,9,17,-1,0,'JP','休場（敬老の日）',0,NULL,NULL,NULL); INSERT INTO "HistoricalDate" VALUES(2,2007,9,17,10,0,'EU','7月貿易収支＜季調前＞',1,'+76億ユーロ','+70億ユーロ','+46億ユーロ'); INSERT INTO "HistoricalDate" VALUES(3,2007,9,17,10,0,'EU','7月貿易収支＜季調済＞',1,'+44億ユーロ','＋40億ユーロ','-6億ユーロ'); INSERT INTO "HistoricalDate" VALUES(4,2007,9,17,13,30,'US','9月NY連銀製造業景気指数',2,'25.1','18','14.7'); INSERT INTO "HistoricalDate" VALUES(5,2007,9,17,16,0,'CH','ロートSNB総裁講演',2,NULL,NULL,NULL); INSERT INTO "HistoricalDate" VALUES(6,2007,9,18,0,50,'JP','7月第3次産業活動指数（前月比）',2,'0.10%','-0.50%','-0.50%'); INSERT INTO "HistoricalDate" VALUES(7,2007,9,18,4,10,'AU','スティーブンスRBA総裁講演',3,NULL,NULL,NULL); INSERT INTO "HistoricalDate" VALUES(8,2007,9,18,8,15,'CH','第2四半期鉱工業生産指数（前期比）',2,'-4.70%','5.20%','6.70%'); INSERT INTO "HistoricalDate" VALUES(9,2007,9,18,9,30,'GB','8月CPI（前年比）',4,'1.90%','1.90%','1.80%'); INSERT INTO "HistoricalDate" VALUES(10,2007,9,18,9,30,'GB','8月CPIコア（前年比）',4,'1.70%','1.80%','1.80%'); ・・・・・・・・・・・・・・・・・・・・・・・ COMMIT;
【SQLiteからMySQLへの移行２】 SQLiteダンプしたSQLのMySQLでの実行
「【SQLiteからMySQLへの移行１】 SQLiteでのDBのダンプ」の続き。
SQLiteでダンプしたDB（SQL文）をMySQLへ投入する。
１．mysqlへログインしてDBへ接続する
C:\>mysql -uroot -p Enter password: ******* Welcome to the MySQL monitor. Commands end with ; or \g. -- データベース一覧の確認 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) -- データベースへの接続 mysql> use test; Database changed -- 接続しているデータベースの確認 mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec)
mysql> source C:\dev\SQLite\EventDateDump.sql
実行した結果、エラーとなった(´Д⊂ヽ、。
mysql> source C:\dev\SQLite\EventDateDump.sql ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRAGM A foreign_keys=OFF' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANS ACTION' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Hist oricalDate] ([id] INTEGER NOT NULL UNIQUE, [nen] INTEGER, [tuki] INTEGER, [' at line 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"Hist oricalDate" VALUES(1,2007,9,17,-1,0,'JP','莨大ｴ・域噴閠√・譌･・・,0,NULL,' at line 1 Query OK, 0 rows affected (0.00 sec)
実行したSQLは下記。ダンプしたファイルのinsert文は1行のみ実行してみた。
PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE [HistoricalDate] ([id] INTEGER NOT NULL UNIQUE, [nen] INTEGER, [tuki] INTEGER, [niti] INTEGER, [zi] INTEGER, [fun] INTEGER, [kuni] TEXT, [event] TEXT, [lank] INTEGER, [mae] TEXT, [yoso] TEXT, [keka] TEXT, PRIMARY KEY(id) ); INSERT INTO "HistoricalDate" VALUES(1,2007,9,17,-1,0,'JP','休場（敬老の日）',0,NULL,NULL,NULL); COMMIT;
SQLの文法が違う（You have an error in your SQL syntax）と言われているので、直す。とりあえず、下記を修正。
・「PRAGMA foreign_keys=OFF;」を削除
・「BEGIN TRANSACTION;」を削除
・create tableで作成するテーブル名を括弧で囲わない
・create tableで作成するテーブルのカラム名を括弧で囲わない
・insert文で指定するテーブル名をダブルクォーテーションで囲わない
実行した結果、成功した。
mysql> source C:\dev\SQLite\EventDateDump.sql Query OK, 0 rows affected (0.10 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
３．テーブルを確認する
（１）テーブルがcreateされているかの確認。
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | historicaldate | | sampletbl | | test2 | +----------------+ 3 rows in set (0.00 sec)
たしかに、historicaldateテーブルが作成されている。
（２）テーブルのデータの確認
selectで確認する。実行結果を１行ずつ整形された形で見るために、「\G」を付けて実行する。
mysql> select * from historicaldate \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 莨大ｴ・域噴閠√・譌･・ lank: 0 mae: NULL yoso: NULL keka: NULL 1 row in set (0.00 sec)
文字化けしている。
文字化けは次回の課題として、データ投入まで完了。
【SQLiteからMySQLへの移行３】 MySQL側での文字化けの解消
前回「【SQLiteからMySQLへの移行２】 SQLiteダンプしたSQLのMySQLでの実行」では、SQLiteでダンプしたデータをMySQLへ投入したが、文字化けしたところまで確認して終わった。
今回はこの原因を突き止めて、解決方法を探る。
１．文字コードの確認
そういえば、character_set_databaseをsjisに変更していたのを忘れていたｗ。
SQLiteはutfがデフォルトでsjisを許容していないので、文字化けの原因は恐らくこれだろう。
mysql> show variables like 'char%'; +--------------------------+--------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | sjis | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\dev\mysql55\share\charsets\ | +--------------------------+--------------------------------+ 8 rows in set (0.00 sec)
ここを参考にすると、次のようにある。
色々な値が表示されますが、特に関係があるのは次の値です。
character_set_client utf8 クライアントが送信する文字コード
character_set_connection utf8 文字コード情報が無い文字列の文字コード
character_set_results utf8 クライアントへ送信する文字コード
character_set_server utf8 サーバのデフォルト文字コード
２．文字コードを変更する
mysql> alter database test character set utf8; Query OK, 1 row affected (0.07 sec) mysql> 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 | C:\dev\mysql55\share\charsets\ | +--------------------------+--------------------------------+ 8 rows in set (0.00 sec)
３．データを再投入してみる
あれれ、文字化けが直らない・・・。
mysql> delete from historicaldate; Query OK, 1 row affected (0.05 sec) mysql> source C:\dev\SQLite\EventDateDump.sql ERROR 1050 (42S01): Table 'historicaldate' already exists Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> select * from historicaldate \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 莨大ｴ・域噴閠√・譌･・ lank: 0 mae: NULL yoso: NULL keka: NULL 1 row in set (0.00 sec)
４．データベースのcharset以外に、テーブルのcharsetも変更する
そういえば、テーブルのcharsetも変更したのだったｗ・・・。
まずは、テーブルのcharsetの確認。
mysql> show create table Historicaldate \G; *************************** 1. row *************************** Table: Historicaldate Create Table: CREATE TABLE `historicaldate` ( `id` int(11) NOT NULL, `nen` int(11) DEFAULT NULL, `tuki` int(11) DEFAULT NULL, `niti` int(11) DEFAULT NULL, `zi` int(11) DEFAULT NULL, `fun` int(11) DEFAULT NULL, `kuni` text, `event` text, `lank` int(11) DEFAULT NULL, `mae` text, `yoso` text, `keka` text, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=sjis 1 row in set (0.00 sec)
DEFAULT CHARSET=sjisとあるので、utf8に戻す。
mysql> alter table Historicaldate character set utf8; Query OK, 1 row affected (0.37 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table Historicaldate \G; *************************** 1. row *************************** Table: Historicaldate Create Table: CREATE TABLE `historicaldate` ( `id` int(11) NOT NULL, `nen` int(11) DEFAULT NULL, `tuki` int(11) DEFAULT NULL, `niti` int(11) DEFAULT NULL, `zi` int(11) DEFAULT NULL, `fun` int(11) DEFAULT NULL, `kuni` text CHARACTER SET sjis, `event` text CHARACTER SET sjis, `lank` int(11) DEFAULT NULL, `mae` text CHARACTER SET sjis, `yoso` text CHARACTER SET sjis, `keka` text CHARACTER SET sjis, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
５．それでも文字化けが直らない
おかしい。。。SJISで開かれている。。。なんでだろう。
mysql> select * from historicaldate \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 莨大ｴ・域噴閠√・譌･・ lank: 0 mae: NULL yoso: NULL keka: NULL 1 row in set (0.00 sec)
コマンドラインに直接貼り付けて実行してみた。
mysql> INSERT INTO HistoricalDate VALUES(1,2007,9,17,-1,0,"JP","休場（敬老の日） ",0,NULL,NULL,NULL); ERROR 1366 (HY000): Incorrect string value: '\x8Bx\x8F\xEA\x81i...' for column ' event' at row 1
これはクライアントの文字コードがutf8に設定されているのに対して、入力文字がshift-jisだからか、返却結果をutf8で解釈しているからだろう。
-- クライアントの文字コードをshift-jisに変更 mysql> set character_set_client=sjis; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO HistoricalDate VALUES(1,2007,9,17,-1,0,"JP","休場（敬老の日） ",0,NULL,NULL,NULL); Query OK, 1 row affected (0.00 sec) mysql> select * from historicaldate \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 莨大ｴ・域噴閠√・譌･・ lank: 0 mae: NULL yoso: NULL keka: NULL 1 row in set (0.00 sec)
クライアント側の文字コードをshift-jisに変更するだけでは文字化け。
そのため、出力結果表示時の文字コードもshift-jisに変更する。
mysql> set character_set_results=sjis; Query OK, 0 rows affected (0.00 sec) mysql> select * from historicaldate \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 休場（敬老の日） lank: 0 mae: NULL yoso: NULL keka: NULL 1 row in set (0.00 sec)
コマンドラインに直接SQLを打ち込んだ場合の文字化けはこれで解消。
これでも、ファイルからSQLを実行した場合は、文字化けが解消しない。
ファイルの文字コードをshift-jisにしたり、utf8にしたりと試したのだが上手くいかない。何でだろう。
mysql> source C:\dev\SQLite\EventDateDump.sql ERROR 1050 (42S01): Table 'historicaldate' already exists Query OK, 1 row affected (0.04 sec) Query OK, 0 rows affected (0.00 sec) mysql> select * from historicaldate \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 莨大?ｴ?域噴閠√?譌･? lank: 0 mae: NULL yoso: NULL keka: NULL 1 row in set (0.00 sec)
原因分からずで、いったん入力となるSQLをSJISで作成して保存して実行した。
DB側の文字コードも全てSJISで実行・・・。
mysql> select * from historicaldate where id between 1 and 10 \G; *************************** 1. row *************************** id: 1 nen: 2007 tuki: 9 niti: 17 zi: -1 fun: 0 kuni: JP event: 休場（敬老の日） lank: 0 mae: NULL yoso: NULL keka: NULL *************************** 2. row *************************** id: 2 nen: 2007 tuki: 9 niti: 17 zi: 10 fun: 0 kuni: EU event: 7月貿易収支＜季調前＞ lank: 1 mae: +76億ユーロ yoso: +70億ユーロ keka: +46億ユーロ
