MySQL : 収集したブックマークデータをデータベースに入れておくのはいかが？

ユーザの多い MySQL が楽そう．

インストール on Ubuntu

Syanptic を用いて "mysql-server"（実体は mysql-server-5.1）をインストール．

インストールを始めると，最初に「MySQLの "root" ユーザに対する新しいパスワード」を求められるから適当な値を設定．

MySQL server の起動と動作チェック

$ sudo mysqld &
$ mysqlshow -u root -p
Enter password: 









































+






















    

    
     Databases      
    










































+






















    

    
 information_schema 
    
    

    
 mysql              
    










































+

$ mysqladmin version -u root -p
Enter password: 
mysqladmin  Ver 8.42 Distrib 5.1.41, for debian-linux-gnu on i486
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version		5.1.41-3ubuntu12.1
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld.sock
Uptime:			6 min 55 sec

Threads: 1  Questions: 313  Slow queries: 0  Opens: 263  Flush tables: 1  Open tables: 23  Queries per second avg: 0.754

ちなみに学生マシンで試して見ると，以下のとおり若干バージョンが異なる。

$ uname -a
Linux aa 2.6.31-20-generic #58-Ubuntu SMP Fri Mar 12 05:23:09 UTC 2010 i686 GNU/Linux

$ mysqladmin version -u root -p
Enter password: 
mysqladmin  Ver 8.42 Distrib 5.1.37, for debian-linux-gnu on i486
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version		5.1.37-1ubuntu5.4
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld.sock
Uptime:			53 sec

Threads: 1  Questions: 315  Slow queries: 0  Opens: 285  Flush tables: 2  Open tables: 23  Queries per second avg: 5.943
$

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.1.41-3ubuntu12.1 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

mysql> 

データベースの作成 ＆ 確認

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

データベースに接続し，テーブル作成

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.1.41-3ubuntu12.1 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed

mysql> create table test_table( no integer, lang varchar(30), primary key(no) ); 
Query OK, 0 rows affected (0.00 sec)

データの追加と確認

mysql> insert into test_table values(1, 'Japanese');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table values(2, 'English');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+----+----------+
| no | lang     |
+----+----------+
|  1 | Japanese |
|  2 | English  |
+----+----------+
2 rows in set (0.00 sec)

mysql> 

MySQLは4.1から文字コードutf8が標準になったそうで，Java との親和性が高い．

文字コード設定の確認

新規で MySQL をインストールした場合、次のように database と server が UTF8 になっていない。
(2010.06.23)

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

そこで、/etc/mysql/my.cnf の [mysqld]セクション、[mysql]セクションへ次の設定を追加した。（ちなみに、これらを ~/.my.cnf に設定しても駄目。）

[mysqld]
default-character-set = utf8
skip-character-set-client-handshake

[mysql]
default-character-set=utf8

その後、mysqld を再起動すると

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)

データベースの削除

drop database test;
Query OK, 1 row affected (0.02 sec)

もう一度、確認

$ mysql -u root -p
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> create table test_table( no integer, lang varchar(30), primary key(no) );
ERROR 1046 (3D000): No database selected
mysql> use test;
Database changed
mysql> create table test_table( no integer, lang varchar(30), primary key(no) );
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test_table;
+----+-----------------+
| no | lang            |
+----+-----------------+
|  1 | 英語            |
|  2 | スペイン語      |
+----+-----------------+
2 rows in set (0.00 sec)

mysql>