taitokissの備忘録

調べたことを忘れないように書き留めています。

MySQL 事始め

MySQL

Webサービスを作りたく、HTML5・CSS3・Javascript・jQery を勉強中というか、いくつかそれらを使ってサイトを作ってみた。

これらも調べたことをまとめなくてはいけないのだが、先に目下勉強中のMySQLを忘れないうちに書いておこうと思う。

これまでは、データの取り扱いはExcelと普通にテキストファイルでの処理で十分だったので、データベースを疎かにしていた。

しかし、ネットで大量のデータを取り扱うには、セキュリティ面やデータの取り扱いに関してデータベースを使わなくてはいけないと、今さらながらMySQLを勉強している次第である。

環境としては、

　Windows10(Ver.1607) に 最新のxampp-win32-5.6.24-1-VC11 をインストールした。

これで、PHP + MySQL + phpMyAdmin が利用できるので便利なものだ。

一応、コマンドラインからバージョンの確認をしておくことにする。

C:￥xampp￥apache￥bin>httpd -V
Server version: Apache/2.4.23 (Win32)

C:￥xampp￥php>php -v
PHP 5.6.24 (cli) (built: Jul 20 2016 21:19:29)

C:￥xampp￥mysql￥bin>mysql --version
mysql Ver 15.1 Distrib 10.1.16-MariaDB, for Win32 (AMD64)

以下、やってみた設定及び操作を書いていく。

○my.iniの設定
　MySQLを使う場合、クライアント側・サーバー側での文字コードの設定を合わせておかないと日本語入力をした時に文字化けが起こる。
　また、MySQL の utf8 は4バイト文字を扱うことができないということで、文字コードは utf8mb4 の設定をしなくてはいけないようだ。

　先に my.ini にデフォルトの文字コードを設定しておかなければならない。
　以下のように、my.ini(origin)の内容を追加・訂正した。（左の数字は行番号）

＞my.ini(origin)
17 # The following options will be passed to all MySQL clients
18 [client]
19 # password = your_password
20 port = 3306
21 socket = "C:/xampp/mysql/mysql.sock"
22
23
24 # Here follows entries for some specific programs

 

＞my.ini(new)
17 # The following options will be passed to all MySQL clients
18 [client]
19 # password = your_password
20 port = 3306
21 socket = "C:/xampp/mysql/mysql.sock"
22 default-character-set=utf8mb4　← 追加
23
24 # Here follows entries for some specific programs


＞my.ini(origin)
27 [mysqld]
151 ## UTF 8 Settings
152 #init-connect=\'SET NAMES utf8\'
153 #collation_server=utf8_unicode_ci
154 #character_set_server=utf8
155 #skip-character-set-client-handshake

156 #character_sets-dir="C:/xampp/mysql/share/charsets"
157
158 [mysqldump]
159 quick
160 max_allowed_packet = 16M
161

162 [mysql]
163 no-auto-rehash
164 # Remove the next comment character if you are not familiar with SQL
165 #safe-updates

166
167 [isamchk]

 

＞my.ini(new)
27 [mysqld]
151 ## UTF 8 Settings
152 #init-connect=\'SET NAMES utf8\'
153 #collation_server=utf8_unicode_ci
154 #collation_server=utf8_general_ci
155 skip-character-set-client-handshake　← # を外す
character_set_server=utf8mb4　← 追加
156 #character_sets-dir="C:/xampp/mysql/share/charsets"
157
158 [mysqldump]
159 quick
160 max_allowed_packet = 16M
161 default-character-set=utf8mb4　← 追加

162 [mysql]
163 no-auto-rehash
164 # Remove the next comment character if you are not familiar with SQL
165 #safe-updates
default-character-set=utf8mb4　← 追加
166
167 [isamchk]

 

○MySQLサーバーへのアクセス

　サーバーの事を知っておく必要がある。
　レンタルサーバー(リモート)
　サーバー用の専用パソコン(ローカル)
　通常使っているパソコンにサーバーソフトを入れる(ローカル)

　レンタルサーバーのMySQLを利用する時のアクセスは、phpMyAdomin を使うのが普通のようだ。もしくはPHPからアクセス。

　ローカルに自分で用意したサーバーの場合は、phpMyAdomin を使いたいならインストールすれば使える。（Xamppに同梱）

　一方、コマンドプロンプトからMySQLを操作するのであれば、まずSQLをある程度知っておく必要がある。

　通常、phpMyAdomin からのアクセスで十分と思うが、細かな操作を行いたい場合は、SQL で指示する必要がある。

　SQL は、phpMyAdomin からも使えるが、コマンドプロンプト からも使える。

　一々、コマンド入力は面倒臭いが基本を知る上で、DBを作るまでの流れをコマンドプロンプトから操作してみることとした。

　以下、その手順を書いておく。

 

１．まず、Xamppコントロールパネルから

f:id:taitokiss:20161103123615j:plain

　「Apache」と「MySQL」を「Start」させておく。

 

２．Windowsコマンドプロンプトを開き、mysql.exe の入っているフォルダをカレントディレクトリにする。

　Xamppの場合、「C:￥xampp￥mysql￥bin」

f:id:taitokiss:20161103104120j:plain

 

３．ユーザとパスワードを指定して、MySQLサーバに接続する。

　オプション「-p」を指定すると、パスワードの入力要求プロンプトが表示される。

・MySQLサーバへの接続方法(1)
 　C:￥xampp￥mysql￥bin> mysql -u ユーザ名 -p
　Enter password: [パスワード]

・MySQLサーバへの接続方法(2)
 　C:￥xampp￥mysql￥bin> mysql -u ユーザ名 -p データベース名
　Enter password: [パスワード]

 

４．操作手順 

　DBが何もないところからのスタートには、予め DB名、ユーザー名、パスワードを決めておくこと。できれば、テーブル名やカラム名なども決めておくとよい。

　以下、例として「uriage」データベースに「hanbai」テーブルと「shouhin」テーブルを作成し、初期データを登録する。
　ユーザーは、root　、パスワードは無し(Enter password:　で何もいれずにEnter)とする。

ａ．mysqlにroot権限でアクセス

・「uriage」DBがない場合
　C:￥xampp￥mysql￥bin> mysql -u root -p

・既に「uriage」DBがある場合
　C:￥xampp￥mysql￥bin> mysql -u root -p uriage

　

　ここでは、まだ「uriage」DBがないので

　C:￥xampp￥mysql￥bin> mysql -u root -p

　と入力し、Enter。

　Enter password:

　このメッセージで、パスワードは無しなので単にEnter。

 f:id:taitokiss:20161103105554j:plain

　MariaDB [(none)]> は、本来 mysql> だが、最新のXamppは MySQL コンパチの MariaDB を搭載しているので MariaDB [(none)]> は、mysql> と同じ。

　コマンドもほぼ同じ？

　MariaDB [(none)]> の none は、DB がまだ未設定の状態。
　DB を指定すると、MariaDB [(DB名)]> の表示となる。

○character_set の確認
　ログイン後にキャラクターセットを確認しておく。

　MariaDB [(none)]> show variables like 'char%';

と入力。

f:id:taitokiss:20161103110031j:plain

my.iniの設定ができているのなら上の様な結果となる。

 

ｂ．「uriage」と言う名前のデータベースを作成

　MariaDB [(none)]> create database DB名 default character set 照合順序文字コード;

 

　MariaDB [(none)]> create database uriage;

と、「default character set 照合順序文字コード」を省略すると、

　MariaDB [(none)]> create database uriage default character set utf8mb4;

を実行したことになる。

 

以下を実行すると、
　MariaDB [(none)]> create database uriage default character set utf8;

照合順序は utf8_general_ci となるが、MySQL の utf8 は4バイト文字を扱うことができないので、照合順序は utf8mb4_general_ci としなくてはいけないようだ。

　この辺りがまだよく分かっていない。

 

　MariaDB [(none)]> create database uriage;

で作成すれば、照合順序は utf8mb4_general_ci となるので、これを実行。

f:id:taitokiss:20161103111104j:plain

　確認は phpMyAdomin で確認すればよい。

 

ｃ．データベースへ接続する

　MariaDB [(none)]> use uriage;

と入力。

プロンプトが、

　MariaDB [(none)]>　から　MariaDB [uriage]>　に変わり、

uriage データベースに接続されたことを確認。

f:id:taitokiss:20161103112931j:plain

 

ｄ．「uriage」データベースに「hanbai」テーブルと「shouhin」テーブルを作成

　MariaDB [uriage]> create table uriage.hanbai(id int not null primary key,shouhin_id int,uriage int);

と入力すれば一発だが、以下のように分割して入力していける。

　MariaDB [uriage]> create table uriage.hanbai(
　　-> id int not null primary key,
　　-> shouhin_id int,
　　-> uriage int);

f:id:taitokiss:20161103113132j:plain

　1行入力Enter毎に、「ｰ>」が出てくる。

f:id:taitokiss:20161103113417j:plain

　最後は「，」ではなく、「；」であることに注意。

続けて、

　MariaDB [uriage]> create table uriage.shouhin(
　　-> id int not null primary key,
　　-> name varchar(20));

を入力し、「shouhin」テーブルを作成。

f:id:taitokiss:20161103113809j:plain

 

ｅ．「testuser@localhost」というユーザーを作成し、「uriage」データベースに対する全ての権限を与える。

　MariaDB [uriage]> create user 'testuser'@'localhost' identified by 'testuser';

　MariaDB [uriage]> grant all on uriage.* to 'testuser'@'localhost';

それぞれを入力。

f:id:taitokiss:20161103114958j:plain

 

ｆ．２つのテーブルに下記の初期データを入力

　MariaDB [uriage]> insert into uriage.shouhin values
　　-> (1,'テレビ'),
　　-> (2,'DVD'),
　　-> (3,'パソコン');

を入力するが、

f:id:taitokiss:20161103115556j:plain

結果が上の様に、２つの Warning が出た。

最初は何か分からなかったのだが、

　MariaDB [uriage]> select * from shouhin;

と入力し、shouhin テーブルの内容を見ると、

f:id:taitokiss:20161103120412j:plain

上の様に、日本語の部分が文字化けしている。

ここで悩んだ。

最初に、「utf8mb4」に設定しているのに何故？

何やかんや調べた挙句、

　Windowsのコマンドプロンプトは「SJIS」にしか対応していない

ということが原因らしい。

対応としては、insert文を実行する前に、

　MariaDB [uriage]> set names cp932;

を実行しておかなければいけないようだ。

この辺りも、もう少し調べる必要がありそうだ。

追記：MySQL set names で調べてみた。

MySQLには文字エンコーディングを変更する「SET NAMES」SQL文が用意されている。この機能はSQLコンソールからは使ってよい機能だが、アプリケーションからは使ってはならない機能とある。
それは、SQLインジェクションが脆弱になる場合があるからだそうだ。

「SQLインジェクション」って何？　また、調べなくては。

ここでは、SQLコンソールでの使用なので良いみたい。

 

一旦、

　MariaDB [uriage]> truncate table shouhin;

で、「shouhin」テーブルの内容を削除し、仕切り直すこととした。

 

　MariaDB [uriage]> set names cp932;

を実行後、再度

　MariaDB [uriage]> insert into uriage.shouhin values
　　-> (1,'テレビ'),
　　-> (2,'DVD'),
　　-> (3,'パソコン');

を実行。

f:id:taitokiss:20161103121744j:plain

Warning が 0 になりました。

　MariaDB [uriage]> select * from shouhin;

で確認すると、

f:id:taitokiss:20161103122001j:plain

今度はしっかり日本語が入っていることを確認。

続けて、「hanbai」テーブルにもデータを入力。

　MariaDB [uriage]> insert into uriage.hanbai values
　　-> (1,1, 320000),
　　-> (2,2, 160000),
　　-> (3,3, 180000),
　　-> (4,1, 128000),
　　-> (5,3, 98000),
　　-> (6,2, 140000),
　　-> (7,1, 175000);

f:id:taitokiss:20161103122558j:plain

両テーブルにデータが入力されたことを確認。

 

ｇ．MySQL(MariaDB)を終了する

　MariaDB [uriage]> ￥q

を実行して、Windowsコマンドプロンプトに戻る。

f:id:taitokiss:20161103123022j:plain

目出度く、スタート地点に戻ってきた。

 

以上、MySQLデータベース及びテーブルの作成からテーブルへのデータ入力までをまとめてみた。

SQL文などは調べれば簡単に分かるのだが、やはり文字コードの部分がネック。
どうにか、ネットで調べてここまでたどり着いたのだが、まだ理解できていない部分が残っていそう。

長くなってしまったので今回はここまで。