MySQL Archive

MySQLにおけるデフォルトの文字コードの変更の仕方はもうクリアしたと思っていたけれど、完全ではなかったようなので、あらためて確認しつつ以下にメモ。

Ubuntu 9.0.4 Server
MySQLはServer version: 5.0.75-0ubuntu10.2 (Ubuntu)

Continue reading

Mac OS X 10.5.7上のVMware FusionにインストールしたCentOS 5.3 ServerにLAMP環境を構築し、PHPとMySQLの接続が出来るかまでを確認するメモ。（PHPはパッケージマネージャを使わないでソースからコンパイルしてインストール）

Continue reading

Ubuntu Server における apache の起動・停止・再起動

$ sudo /etc/init.d/apache2 start
$ sudo /etc/init.d/apache2 stop
$ sudo /etc/init.d/apache2 restart

または（パスの指定がないので、こっちのほうが簡単）

$ sudo apache2ctl start
$ sudo apache2ctl stop
$ sudo apache2ctl restart

Ubuntu Server における MySQL の起動・停止・再起動

$ sudo /etc/init.d/mysql start
$ sudo /etc/init.d/mysql restart
$ sudo /etc/init.d/mysql stop

MySQLとApacheの起動、再起動、停止について理解が曖昧＆間違っていたので、あらためてメモ。

my.cnf （MySQL の設定ファイル）を編集した場合、この編集内容を有効にするには MySQL Server の再起動が必要。
（この設定ファイルの場所は /etc/mysql/my.cnf ）

以下、上から順に起動、再起動、停止のコマンド。

$ sudo /etc/init.d/mysql start
$ sudo /etc/init.d/mysql restart
$ sudo /etc/init.d/mysql stop

これは Ubuntu Server でのもの。（Linux全般そうなのかな？）

MacPortsで構築した環境だと以下のコマンドになる。
（ついでに Apache についても記しておく）

MySQL（version 5 の場合）

$ sudo /opt/local/share/mysql5/mysql/mysql.server start
$ sudo /opt/local/share/mysql5/mysql/mysql.server restart
$ sudo /opt/local/share/mysql5/mysql/mysql.server stop

Apache（version 2 の場合）

$ sudo /opt/local/apache2/bin/apachectl stop
$ sudo /opt/local/apache2/bin/apachectl start

Ubuntu Server にインストールした
mysql データベースの文字コードを utf-8 に変更する。

デフォルトだと latin1 になっている。以下のように。

mysql> show variables like ‘char%’;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)

mysql の設定ファイル my.cnf は
/etc/mysql/ にあるので、このファイルに以下のように記述を追加。
（utf-8などと、ハイフンを入れないように注意）

[client] のブロックに
default-character-set=utf8

[mysqld] のブロックに
default-character-set=utf8
skip-character-set-client-handshake
を追記。

[mysql.server]
user=mysql
default-character-set=utf8
を適当な場所に追加。

[mysqld_safe] のブロックに
default-character-set=utf8
を追記。

[mysql] のブロックに
default-character-set=utf8
を追記。

[mysqldump] のブロックに
default-character-set=utf8
を追記。

[mysqld] ブロックの
language = /usr/share/mysql/english
の english を japanese に変更。

設定ファイルの設定を有効にするために、MySQL を再起動させる。

MySQL の開始、再起動、停止

$ sudo /etc/init.d/mysql start
$ sudo /etc/init.d/mysql restart
$ sudo /etc/init.d/mysql stop

確認してみると

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 | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.00 sec)

全部 utf-8 になった(･∀･)ｲｲ

ちなみに、character_set_filesystem の部分は binary で問題ないらしい。
（ファイルシステムのための文字コードなので）

これの続きで、今度は同じことをPEAR::DBというパッケージを利用してやってみる。

Continue reading

抽象化レイヤ（PEAR::DBとかADOdbとかMDB2とか）を使わずに、PHPにおけるMySQL操作の専用関数を使用してMySQLを操作してみる。（これの続き）

Continue reading

◆データベースを作成
（データベースの名前はphplesson）

mysql> CREATE DATABASE phplesson;
Query OK, 1 row affected (0.00 sec)

◆テーブルを作成
（操作を実行するデータベースをUSEで指定してから行う）

mysql> USE phplesson;
Database changed
mysql> CREATE TABLE address_t (
    -> number INT,
    -> name VARCHAR(50),
    -> birthday DATE,
    -> address VARCHAR(255),
    -> freespace TEXT);
Query OK, 0 rows affected (0.00 sec)

◆作成したテーブル情報を確認

mysql> DESC address_t;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| number    | int(11)      | YES  |     | NULL    |       |
| name      | varchar(50)  | YES  |     | NULL    |       |
| birthday  | date         | YES  |     | NULL    |       |
| address   | varchar(255) | YES  |     | NULL    |       |
| freespace | text         | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

◆主キー（プライマリキー）を設定

主キーは、テーブル内のフィールドを一意（ユニーク）にするためのもの。値がNULLではいけない。また、重複した値は登録できなくなる。

カラム「number」に主キーを設定

mysql> ALTER TABLE address_t ADD PRIMARY KEY(number);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

どうなったか確認してみる

mysql> DESC address_t;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| number    | int(11)      | NO   | PRI | 0       |       |
| name      | varchar(50)  | YES  |     | NULL    |       |
| birthday  | date         | YES  |     | NULL    |       |
| address   | varchar(255) | YES  |     | NULL    |       |
| freespace | text         | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

numberのKeyの属性がPRIになっている。（主キーに設定された）

◆新しいカラムを追加

address_tテーブルに新しく「mail」という名前のカラムを追加

mysql> ALTER TABLE address_t ADD mail VARCHAR(128);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

確認してみる

mysql> DESC address_t;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| number    | int(11)      | NO   | PRI | 0       |       |
| name      | varchar(50)  | YES  |     | NULL    |       |
| birthday  | date         | YES  |     | NULL    |       |
| address   | varchar(255) | YES  |     | NULL    |       |
| freespace | text         | YES  |     | NULL    |       |
| mail      | varchar(128) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

◆作成済みのカラムのカラム名とデータ型を変更

カラム「freespace」の名前と、このカラムのデータ型を変更してみる
（BLOBは可変長で長いバイナリテキスト。文字数でなくバイト数でカウント）

mysql> ALTER TABLE address_t CHANGE freespace explanation BLOB;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

確認してみる

mysql> DESC address_t;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| number      | int(11)      | NO   | PRI | 0       |       |
| name        | varchar(50)  | YES  |     | NULL    |       |
| birthday    | date         | YES  |     | NULL    |       |
| address     | varchar(255) | YES  |     | NULL    |       |
| explanation | blob         | YES  |     | NULL    |       |
| mail        | varchar(128) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

◆データの挿入

文字列の値は引用符で囲うことが必要。数値型の値はそのままで大丈夫。でも、つけ忘れを防ぐためにも、データの値を追加するときは文字でも数値でも引用符「’data’」で囲うようにしておいたほうがいい。

mysql> insert into address_t values ('1','suzuki','1980-01-01','TokyoNakanoku','setsumei','suzuki@hogehoge.hoge');
Query OK, 1 row affected (0.02 sec)

確認してみる

mysql> select * from address_t;
+--------+--------+------------+---------------+-------------+----------------------+
| number | name   | birthday   | address       | explanation | mail                 |
+--------+--------+------------+---------------+-------------+----------------------+
|      1 | suzuki | 1980-01-01 | TokyoNakanoku | setsumei    | suzuki@hogehoge.hoge |
+--------+--------+------------+---------------+-------------+----------------------+
1 row in set (0.00 sec)

◆データの更新

numberが1のsuzukiさんのaddressをTokyoNakanokuからTokyoSuginamikuに変更してみる。

mysql> update address_t set address='TokyoSuginamiku' where number='1';

このクエリの意味は、address_tテーブル内のnumberカラムの値が1のフィールドにおいて、addressの値をTokyoSuginamikuに変更するということ。
（whereで変更する対象を指定しないと、全てのフィールドが変更されてしまうので注意）

データが更新できたか確認してみる。

mysql> select * from address_t;
+--------+--------+------------+-----------------+-------------+----------------------+
| number | name   | birthday   | address         | explanation | mail                 |
+--------+--------+------------+-----------------+-------------+----------------------+
|      1 | suzuki | 1980-01-01 | TokyoSuginamiku | setsumei    | suzuki@hogehoge.hoge |
+--------+--------+------------+-----------------+-------------+----------------------+
3 rows in set (0.00 sec)

◆テーブル内のデータを削除

mysql> delete from address_t where number='1';

このクエリの意味は、address_tテーブル内のnumberカラムの値が1のフィールドの全データを削除するということ。
これも、where句を使って範囲指定をしないと全てのフィールドが削除対象になるので注意。

確認すると
（2と3は後から追加した）

mysql> select * from address_t;
+--------+-------+------------+----------------+-------------+---------------------+
| number | name  | birthday   | address        | explanation | mail                |
+--------+-------+------------+----------------+-------------+---------------------+
|      2 | saito | 1978-01-01 | TokyoShibuyaku | setsumei    | saito@hogehoge.hoge |
|      3 | ito   | 1979-01-01 | TokyoToshimaku | setsumei    | ito@hogehoge.hoge   |
+--------+-------+------------+----------------+-------------+---------------------+
2 rows in set (0.00 sec)

deleteしたフィールドのデータが削除されている。

◆テーブル内のデータを検索する

address_tテーブル内のnumberカラムの値が1のフィールドデータ全てを取得してみる。

mysql> select * from address_t where number='1';
+--------+--------+------------+----------------+-------------+----------------------+
| number | name   | birthday   | address        | explanation | mail                 |
+--------+--------+------------+----------------+-------------+----------------------+
|      1 | suzuki | 1989-09-01 | TokyoShibuyaku | setsumei    | suzuki@hogehoge.hoge |
+--------+--------+------------+----------------+-------------+----------------------+
1 row in set (0.00 sec)

今度は、同じフィールドで、nameカラムの値だけを取得。

mysql> select name from address_t where number='1';
+--------+
| name   |
+--------+
| suzuki |
+--------+
1 row in set (0.01 sec)

◆SELECT文のWHERE句で使用できる演算子の例
（以下の例は全てaddress_tテーブルが対象）

birthdayの値が1980未満のデータを取得したい場合（演算子<を使用）

mysql> select * from address_t where birthday < '1980';

numberの値が1と2と5と7のデータを取得したい場合（演算子inを使用）

mysql> select * from address_t where number in ('1','2','5','7');

numberの値が1と2と5と7ではないデータを取得したい場合（演算子not inを使用）

mysql> select * from address_t where number not in ('1','2','5','7');

and、or、notを使用することで条件を複数つなげて指定することもできる。

birthdayの値が1970以上、1980未満のデータだけを取得したい場合（演算子>=とandを使用）

mysql> select * from address_t where birthday >= '1970' and birthday < '1980';

addressの値にTokyoToshimakuを含むもの、またはTokyoShibuyakuを含むものを取得したい場合（演算子=とorを使用）
＊ようするに、値にTokyoToshimakuを含む、もしくはTokyoShibuyakuを含むもの（どちらも存在すれば両方ともということ）

mysql> select * from address_t where address = 'TokyoToshimaku' or address = 'TokyoShibuyaku';

◆WHERE句で使用できる条件式

likeを使って曖昧検索（〜〜を含むデータを取得）をする。
＊任意の一文字を表す_や、0以上の任意の文字を表す%等のワイルドカードを使って。

like 'P_P'（PHPやPAPが一致する：前方一致）
like 'A%'（Aから始まるデータが一致：前方一致）
like '%A'（Aで終わるデータが一致：後方一致）
like '%A%'（Aを含むデータが一致）

birthdayの値が19?0を含むデータを取得する場合

mysql> select * from address_t where birthday like '19_0%';

addressの値にShiを含むデータを取得する場合

mysql> select * from address_t where address like '%Shi%';

◆MySQLで使用出来る関数

任意の文字列をフィールドから切り出して取得する関数substringとright

address_tテーブルにおいて、nameカラムの値（文字列）の1文字目から4文字目までを取得する

mysql> select substring(name, 1, 4) from address_t;

address_tテーブルにおいて、nameカラムの値（文字列）を右端から4文字取得する

mysql> select right(name, 4) from address_t;

日付の関数（年のみを返す）

address_tテーブルにおいて、birthdayカラムの値から年のみを取得する

mysql> select year(birthday) from address_t;

right関数とyear関数を併用することで、以下のようにして生年月日から現時点での年齢を知ることも出来る。

mysql> select name, birthday, curdate(), (year(curdate())-year(birthday))-(right(curdate(),5)<right(birthday,5)) as age from address_t;

＊curdate()は現在の日付を返す関数。as名前とすることで、得られた結果のフィールド名をつけることができる。

◆order byでデータを並び替える

select文で取得したデータを並び替えるのにorder byを使う。
ascは昇順（小さい順）、descは降順（大きい順）に並び替える。
＊省略すると昇順。

birthdayカラムの値を昇順で並び替えて取得

mysql> select * from address_t order by birthday asc;

birthdayカラムの値を降順で並び替えて取得

mysql> select * from address_t order by birthday desc;

件数を指定してデータを取得したい場合はlimitを使う。

address_tテーブルにおいて、先頭から3件目までのデータのみを取得する場合

mysql> select * from address_t limit 0,3;

◆group byでデータをグループ化する

値をグループ化することで、そのグループにおけるデータの合計や平均値などを求めることが出来る。

max：グループ化したデータの最大値を取得
min：グループ化したデータの最小値を取得
sum：グループ化したデータの合計を取得
avg：グループ化したデータの平均値を取得
count：グループ化したデータのデータ数をカウント

birthdayカラムの値でグループ化し、その値（年）がそれぞれ何人いるかを取得する

mysql> select year(birthday),count(*) as num from address_t group by year(birthday);

＊birthdayカラムの値をyear関数を使って年だけ取り出し、その年にあたるデータ数をcountでカウント。得た値をas numで「num」として表示している。

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

CREATE FUNCTIONを有効にするには、作成時にDETERMINISTICもしくはNO SQL、READS SQL DATAの中から一つを選んで明確に指定しないといけないらしい。これをしないと上記のようなエラーが発生する。（エラー内容にもそのように出ている）

Continue reading

localhostにおけるrootユーザのパスワードを変更するには

mysql> SET PASSWORD FOR root@localhost=PASSWORD(’hoge’);

hogeの部分に新しいパスワード、この部分を空にすればパスワードは設定なしになる。
パスワードなしにした場合もパスワードは聞かれるが、そのままenterキーをおせばMySQL moniterを起動できる。