Top > MySQL

MySQL のリファレンスマニュアルはけっこう読みやすいので、ちゃんとこれを読みましょう。
http://dev.mysql.com/doc/refman/5.1/ja/index.html
osCommerce の MySQL クイックリファレンス
http://www.bitscope.co.jp/tep/MySQL/quickMySQL.html

関連ページ:文字コード/MySQL+Javaの文字化け

クイックリファレンス Edit

 ■ コマンド
 ? または show          // コマンドのヘルプを表示する
 desc hoge;             // テーブル hoge の定義を確認する
 show create table hoge; // テーブル hoge の定義を詳しく表示する
 show databases;        // データベース一覧を表示
 show tables;           // テーブル一覧を表示
 show warnings;         // 前回の SQL で出た warning の表示をする
 show variables [like 'hoge%'];  // システム変数を表示する
 use hoge;              // データベース hoge を使う(スキーマの切り替え)
 status;                // 現在のセッションや文字コードの情報
 \. hoge.sql;           // hoge.sql を読み込んで実行する

 ■ 関数
 length('hoge')          // => 4  文字列の長さ(バイト単位)
 char_length('あ')       // => 1  文字列の長さ(文字単位)
 concat('a','b')         // => 'ab' 文字列の連結
 hex('hoge')             // => 686F6765 16進ダンプ
 upper('hoge')           // => 'HOGE' 大文字に変換
 lower('HOGE')           // => 'hoge' 小文字に変換
 date_format(now(),'%Y/%m/%d %H:%i:%s')    // 現在の日付・フォーマット

MySQL初心者がはじめに知っておくべきこと Edit

MySQL の特徴 Edit

MySQL は Oracle とは違う点が多いので、最初に Wikipedia などでその特徴を確認しておくといいです。
http://ja.wikipedia.org/wiki/MySQL

  • デフォルトのストレージエンジン MyISAM はトランザクションをサポートしていない。
  • バージョン4.1以降、文字化けの問題が多くなった(適切に設定すれば問題ないが設定が難しい)。
  • シーケンスがない。代わりにauto_incrementを使う
    (そのため、主キーが2つの連番IDの複合キーになっているテーブルにインサートするのがやりづらい。逆に代理キーを使う設計に向いている。なお、auto_incrementの初期値は通常は1。)。
    自分でテーブルを作ってシーケンスをエミュレートする方法もある。
  • 普通にテーブルを作ると = での検索で大文字・小文字を区別しない。区別させるには binary 属性を使う。
  • 空文字列とNULLは区別される。(Oracle以外ほとんどのRDBMSは区別する)
  • ビューは5.0からサポート。更新可能ビューも作れる。materialized viewはない。
  • ストアドプロシージャは5.0からサポート。

ストレージエンジン Edit

MySQL にはストレージエンジンが何種類もある。
代表的なのは MyISAM と InnoDB。大きな違いは:

  • MyISAM はトランザクションをサポートしていない。(よって rollback もできない!)
  • MyISAM はロックをテーブル単位でしかかけられない。(よって select ... for update もできない!)
    そして
  • 検索中心ならMyISAMの方が速い
  • しかし今はInnoDBもかなり速い
  • MyISAMの方が管理が簡単

デフォルトを確認するには

mysql> show variables like 'storage%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+

普通は MyISAM がデフォルトのはず。

MyISAM vs InnoDB - naoyaのはてなダイアリー
http://d.hatena.ne.jp/naoya/20060729/1154139996
MySQL の MyISAM について質問です。
http://q.hatena.ne.jp/1147863300
Wikipedia : MySQL 5.0 では参照系でも InnoDB の方が速いことも多い
http://ja.wikipedia.org/wiki/MySQL

各データ型の最大値 Edit

MySQL 5.1 の情報。

数値型、日付・時刻型

バイト最小値最大値
TINYINT1-128127
SMALLINT2-3276832767
MEDIUMINT3-83886088388607
INT(INTEGER)4-21474836482147483647
BIGINT8-92233720368547758089223372036854775807
DATE-1001-01-019999-12-31*1
DATETIME-1000-01-01 00:00:009999-12-31 23:59:59*2

文字列型

最大サイズバイト
VARCHAR6553265533*3
TINYTEXT または TINYBLOB2^8-1255
TEXT または BLOB2^16-1(64K-1)65535
MEDIUMTEXT または MEDIUMBLOB2^24-1(16M-1)16777215
LONGBLOB2^32-1(4G-1)4294967295

各データ型の必要容量
http://dev.mysql.com/doc/refman/5.1/ja/storage-requirements.html

auto_increment Edit

auto_incrementは1から。
レコードをdeleteすると、そこは欠番となり、次回insertするレコードはmax(id)+1となる。

MyISAMでトランザクションがないのにどうやってプログラミングするの? Edit

LOCK TABLES でテーブルをロックします。

トランザクションをサポートしていないストレージエンジンを MySQL で使用している場合、SELECT と UPDATE の間に他のスレッドに割り込まれないようにするには、LOCK TABLES を使用する必要がある。次の例では、安全に処理を実行するために LOCK TABLES を発行する必要がある。 

mysql> LOCK TABLES trans READ, customer WRITE;
mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;
mysql> UPDATE customer SET total_value=sum_from_previous_statement
    ->        WHERE customer_id=some_id;
mysql> UNLOCK TABLES;

http://dev.mysql.com/doc/refman/4.1/ja/lock-tables.html

varcharの文字数はバイト数?それとも文字数? Edit

varchar(10) のカラムに格納できるのは10バイトか10文字か?
答えはDBの文字コードによる。
文字コードが binary の場合は10バイト。utf8 の場合は utf8 で10文字。

こういった問題は、以下のようにして必ず実験してみること。

mysql> create table x (a varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into x (a) values ('あいうえおかきくえこさしすせそ');
Query OK, 1 row affected (0.00 sec)

プログラミング系 Edit

auto_incrementでinsertされたレコードを取得するには? Edit

キーとなるカラムをAUTO_INCREMENTにするのはよくある事で、データをInsertしてから、InsertしたレコードのAUTO_INCREMENT値を取得したいというのはよくある事です。

方法としては2つあり、PHPの関数 mysql_insert_id() で取得する方法と、MySQLの関数 last_insert_id() で取得する方法があります。
アクセス数が多く、Insertがほぼ同時に行われた場合に返ってくる値が別のレコードのものだったらどうしよう…という点については心配する必要がないです。同じクライアントからのアクセス内での直近Insertの値を返すようになっているので、それぞれ正しい値が返ってきます。
http://blog.tofu-kun.org/071206185929.php

テキストファイルからレコードをインポートするには?(mysqlimportよりLOAD DATA) Edit

mysqlimport が Oracle の SQL Loader に相当する。
しかし、mysqlimportでは警告の件数しか表示されず、警告の内容が表示されない。
警告が欲しい場合にはLOAD DATAを使うしかない。
警告の内容はどうせ必要になるため、初めからLOAD DATAを使うようにしたほうがよい。

LOAD DATA Edit

SQL="DELETE FROM $TABLE_NAME; LOAD DATA LOCAL INFILE '$FILE_NAME' REPLACE INTO TABLE $TABLE_NAME IGNORE 1 LINES;"
mysql --verbose --local-infile=1 --show-warnings -u "$USER" -p"$PASSWORD" -e "$SQL" "$DB_NAME"
  • local-infile=1でないといけないことに注意。このオプションはmy.cnfでも設定できる。

LOAD DATAステートメントを実行する直前に暗黙にCOMMITが行われる。

mysqlimport Edit

mysqlimport -u USERNAME -p --local DBNAME TABLENAME.txt

ここで TABLENAME はインサートしたいテーブル名と同じでなくてはならない。
TABLENAME.txt の中身はタブ区切り。

オプション -d (--delete)をつけると、最初にテーブルの全行を削除する。
オプション -r (--replace)をつけると、unique 制約に違反する場合や primary key がかぶる場合、上書きしてくれる。
つまり、 -d -r と同時に指定すれば、テーブルの中身をファイルの中身と一致させることができる。これは便利。
オプション --ignore-lines=#をつけると、最初の#行を無視する。

あいまい検索を実装するには? Edit

Levenshtein distanceを計算するストアドファンクション。
しかしLevenshteinは大量の検索に使うにはちょっと効率が悪い。
http://www.artfulsoftware.com/infotree/queries.php#552

warning の内容を表示するには? Edit

mysql> insert into x (a) values ('あいうえおかきくえこさしす');
Query OK, 1 row affected, 1 warning (0.00 sec)

↑ 1 warnings と出ているが、この内容はなんなのか?
show warnings を使う。

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
warnings;

をしておくと、毎回 SQL の実行後自動的にこの内容が表示されるようになる。

文字化けが起きたら? Edit

まず 文字コード関連システム変数を確認します。
そして hex() を使ってレコードの中身を16進ダンプし、データが正しく保存されているか見てみます。

mysql> select a,hex(a) from x;
+-----------+--------------------+
| a         | hex(a)             |
+-----------+--------------------+
| あいう    | E38182E38184E38186 |
+-----------+--------------------+
1 row in set (0.00 sec)

「あいう」は UTF-8 で「E3 81 82 E3 81 84 E3 81 86」なので、データは正しく保存されています。
これで文字化けが発生するということは、データ取得時にクライアントの文字コードへ変換するときに化けているのかも。

インサート時、データが長すぎると成功するがwarningになり、自動的に切り詰められる Edit

mysql> create table x (a varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into x (a) values ('ABCDEFGHIJKL');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from x;
+------------+
| a          |
+------------+
| ABCDEFGHIJ |
+------------+
1 row in set (0.00 sec)

CREATE FUNCTION(ストアドファンクションの作り方) Edit

CREATE FUNCTIONするときはDELIMITERを変えておく。

DELIMITER //
CREATE FUNCTION gettime(o integer, l smallint) RETURNS datetime
BEGIN
DECLARE ret datetime;
SELECT MAX(time) INTO ret FROM t2 WHERE t2.order_id = o AND t2.level = l;
RETURN ret;
END;
//
DELIMITER ;

削除は

DROP FUNCTION gettime;

管理系 Edit

設定ファイルの場所 /etc/my.cnf Edit

/etc/my.cnf。
Ubuntu では /etc/mysql/my.cnf。

my.cnf のパーミッションが誰でも書き込めるようになっていると、MySQL はそのファイルを読み込まない。
以下のような警告を出す。

Warning: World-writable config file '/etc/my.cnf' is ignored

このコマンドで修正できる。

chmod 644 my.cnf

my.cnfのサンプル

[client]
user=USER
password=PASSWORD

[mysql]
database=DATABASE
show-warnings
auto-rehash
pager=less
local-infile=1

[mysqlimport]
local

DBのファイル格納場所 /var/lib/mysql Edit

/var/lib/mysql

の中に各データベース名のディレクトリがある。

ログファイルの場所 /var/lib/mysql Edit

/var/lib/mysql/ホスト名.err

今までに実行した SQL のログを見るには?(mysqlbinlog) Edit

/var/lib/mysql に -bin がつくファイルがある。これが Oracle の REDO ログみたいなもので、
最後にバックアップして以来の全更新 SQL が記録されている。

mysqlbinlog -s /var/lib/mysql/mysql-bin.000003   (最後のファイル名は例)

で今までに実行した更新系のSQL だけは表示できる。
しかしシステムが自動的に発行した SQL も記録されているし、SELECT 文は表示されないので
開発時のデバッグ用としては使いづらい。

今までに実行されたSQLのログを見るには?(--log) Edit

http://blog.tofu-kun.org/070721115104.php#extended

ユーザ作成 Edit

GRANT ALL PRIVILEGES ON *.* TO user@"%" IDENTIFIED BY 'password' WITH GRANT OPTION;

'password'は設定したいパスワード。

確認

SELECT user, host FROM mysql.user;
SHOW GRANTS FOR user@localhost;

ユーザ削除 Edit

DROP USER 'user'@'localhost';

○○○の一覧を見るには? Edit

ユーザー一覧

select user from mysql.user;

データベース一覧

show databases

テーブル一覧

show tables

ビュー一覧
show viesはない。

show tables

で出てくる。
トリガー一覧

show triggers

OracleのDBA_TABLES相当(information_schema) Edit

MySQL :: MySQL 5.1 リファレンスマニュアル :: 21 INFORMATION_SCHEMA データベース
http://dev.mysql.com/doc/refman/5.1/ja/information-schema.html

information_schemaがOracleのデータディクショナリ・ビューに相当する。

テーブル一覧をselectする。

>select table_name,table_type,engine from information_schema.tables where table_schema='DBNAME';
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| composer   | BASE TABLE | MyISAM |
| hoge3      | BASE TABLE | MyISAM |
+------------+------------+--------+
2 rows in set (0.05 sec)

http://kanitotomato.dip.jp/~ao/pp/index.php?cmd=edit&page=MySQL&id=kec34ba9

全テーブルを一度にdescするには? Edit

MySQLにはそのようなコマンドは用意されていないのでスクリプトを作る必要がある。

USER="xxx"
DB="yyy"
 echo "show tables" | mysql -u "$USER" "$DB" | sed -e "1d; s@.*@select '&' from dual; desc &;@" | mysql -H -u "$USER" "$DB"

一時的に制約を無効にするには? Edit

外部キー制約を無効にする。

set foreign_key_checks = 0

http://dev.mysql.com/doc/refman/5.1/ja/innodb-foreign-key-constraints.html

現在のコネクション数を知るには? Edit

(es@localhost)[es]> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     | 
| Threads_connected | 1     | 
| Threads_created   | 5276  | 
| Threads_running   | 1     | 
+-------------------+-------+
4 rows in set (0.00 sec)

テーブルを複製するには?(データはコピーしない) Edit

create table 新テーブル like 元テーブル;

テーブルを複製するには?(データもコピーする) Edit

create table 新テーブル like 元テーブル;
insert into 新テーブル select * from 元テーブル;

行を複製するには? Edit

一時テーブルを作成して行をコピーし、必要な部分だけ変更してから元テーブルにコピーする。

create temporary table 新テーブル like 元テーブル;
insert into 新テーブル select * from 元テーブル where 条件;
update 新テーブル set 適当 where 条件;
insert into 元テーブル select * from 新テーブル;

mysqlセッションを抜けると一時テーブルはあとかたもなく削除される。

mysqldump Edit

基本(mysqldump -u USER --password=PASS DATABASE_NAME > db.sql) Edit

全データベース(mysqldump -u USER --password=PASS --all-databases > all-databases.sql) Edit

テーブルをタブ区切りテキストファイルにダンプ Edit

  • Tでディレクトリを指定。mysqlプロセスが書き込めるディレクトリを指定しなければならない。
    mysqldump -u USER --password=PASS DATABASE_NAME TABLE_NAME -T /tmp

このコマンドを実行するホストとmysqlサーバのプロセスを実行しているホストが同じでなければならない。
同じでない場合は、mysql -B コマンドが代替手段になる。

テーブル定義だけをダンプ(-d) Edit

mysqldump -u USER --password=PASS -d DATABASE_NAME > schema.sql

複数の開発環境でdiffをとったりするのに便利。

文字コード Edit

現在の設定を表示 Edit

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     | utf8                           |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+

ちなみにこれはPHPからでも普通にqueryできる:

$rs = $db->getAll("show variables like 'char%'");

文字コード関連システム変数 Edit

MySQL 5.1
http://dev.mysql.com/doc/refman/5.1/ja/server-system-variables.html

character_set_client
クライアント送信の文字列のキャラクタ セット (クライアントが送信するキャラクタ セット)。
character_set_connection
キャラクタ セット情報がないリテラルの並びで、数値→文字と変換するときのキャラクタ セット。
character_set_database
デフォルト データベースで使用するキャラクタ セット。デフォルト データベースが変化する度に、サーバがこの変数を変更する。デフォルト データベースが存在しない場合、この値は character_set_server と同一。
character_set_filesystem
ファイルシステムのキャラクタ セット。LOAD DATA INFILE や SELECT ... INTO OUTFILE などのステートメントや LOAD_FILE() 関数に対して、この変数でファイル名とリテラルの文字列を読み取る。ファイルを開けようとすると、ファイル名が character_set_client から character_set_filesystem に変わる。デフォルト値は binary (変換が起こらない) である。マルチ バイトのファイル名を利用できるシステムでは、異なる値を使用することが好ましい。たとえば、UTF-8 でファイル名を表示しているシステムの場合は、character_set_filesytem を 'utf8' にセットする。(MySQL 5.1.6 実装)
character_set_results
クライアントへ返す文字列 (クエリ結果) のキャラクタ セット。
character_set_server
サーバのデフォルトのキャラクタ セット。
character_set_system
識別子の書き出しにサーバが使用するキャラクタ セット。この値は常に utf8。

サーバは受け取ったデータが character_set_client でエンコーディングされていると仮定し、
内部で client→connection の変換を行う。

サーバから送るときは results でエンコーディングする。

静的設定 Edit

  • サーバ
    [mysqld]
    default-character-set = utf8
    
  • クライアント(mysqlコマンド)
    [mysql]
    default-character-set = utf8
    

default-character-set は character-set-server をうけて将来的に廃止予定。

動的変更 Edit

$db->query("set names utf8");

auto-reconnect をオンにして mysql コマンドを使っている場合は、

set names utf8;

より

charset utf8;

の方がよい。こうすると再接続のたびに自動的に set names utf8; を実行してくれる。

文字コード名 Edit

以下のコマンドで使える文字コード名を全て表示。

mysql> show character set;

代表的なもの

| binary   | Binary pseudo charset       | binary              |      1 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |

基本的にcp932/eucjpms/utf8は相互に変換できるように作られている(しかしutf8にしかない文字をcp932/eucjpmsに変換しようとした場合などは当然化ける)。
sjis/ujisは九バージョンの名残であり、MySQL 5.0の環境であれば、sjis/ujisを使う必要はないだろう。
参考:現場で使えるMySQL

インストール/環境設定 Edit

localhost からは接続できるが、他のマシンから接続できない Edit

MySQL は localhost からの接続権限と他のマシンからの接続権限を別々に設定できるので、その辺を確認してみること。
http://www.rfs.jp/server/mysql/02/02.html

クライアントの環境設定 Edit

/.my.cnf に書く。

[mysql]
# warning が発生したとき、自動的に内容を表示する。
show-warnings
# コマンドラインのプロンプト(例:(root@localhost) [test]> ) \_はスペース
prompt=(\u@\h) [\d]>\_
# タブで補完が効くようにする
auto-rehash

コマンドラインmysqlでselectの結果が長いと折り返されて見づらい Edit

SQLの最後に「\G」をつけると見やすくなる。

select * from tb_user \G

http://norainu.net/mt/archives/2006/09/mysql_g.html

リンク集 Edit

薫のhack
http://paranoid.dip.jp/kaworu/cat_mysql.html



URL B I U SIZE Black Maroon Green Olive Navy Purple Teal Gray Silver Red Lime Yellow Blue Fuchsia Aqua White

*1 サポート範囲より前の値でも動作する場合がありますが、確実に動作するという保証はありません
*2 サポート範囲より前の値でも動作する場合がありますが、確実に動作するという保証はありません
*3 1レコードのバイト数は最大65536という制限があるため実際はもっと小さい

Reload   New Lower page making Edit Freeze Diff Upload Copy Rename   Front page List of pages Search Recent changes Backup Referer   Help   RSS of recent changes