よく使うコマンド一覧 <span style="color:#FF0000;">更新中</span>

起動

/usr/local/mysql/bin/mysqld_safe & 
sudo launchctl unload -w /Library/LaunchDaemons/com.mysql.mysqld.plist ← Leopard

停止

./mysqladmin -u root shutdown
sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysqld.plis ← Leopard

DB作成

create database dbname default character set utf8;

テーブル定義変更

 ALTER TABLE hoge_talbe MODIFY
    hoge CHAR(7);

文字コード確認

 show variables like 'character%';
文字コード変更
SET CHARACTER SET utf8
||

** INDEX確認

>||
SHOW INDEX FROM tbl_name

テーブル状態確認 ENGINEも確認可能

その1
SHOW TABLE STATUS;
CREATE文作成
SHOW CREATE TABLE TABLENAME;

AUTO_INCREMENT のカウンターを リセットする

ALTER TABLE [table_name] AUTO_INCREMENT=0;

起動方法

mysqldump -u root -ppass -x databasename > hoge.txt
一定の条件を満たすレコードのみdumpする
$ mysqldump -u ユーザ名 -p -t "--where=カラム名='文字列'" データベース名 テーブル名 > ファイル名

DDLのみ作成

mysqldump -u root --no-data dbname > db_ddl.sql

NULL値置き換え

参考URL

COALESCE(list)

リスト内の最初の非 NULL 要素を返す。

mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL

DB名の変更

MySQL4系で試した。

以下のように/var/lib/mysqlの下のDBディレクトリ名を変更するだけ。
ただ、権限とかの設定はうまく引き継がれない?ようだが、よくわからない。
mv /var/lib/mysql/dbname /var/lib/mysql/dbname2

起動プロセスを調べる

DBの検索で、まったくSQL文の反応がデータが多すぎて無いことがある。
その際にmysqlのプロンプトからAbortedしてしまうとプロセスが残ってしまい、
そのまま負荷をかけ続ける事がある。

そんなときはここを参考にする。

mysql> SHOW PROCESSLIST; ← ここでプロセス番号を確認
|   111111      | system user |             | lm        | Connect | 0        | update                           | SELECT id, money, createtime * FROM ・・・ |
mysql> KILL プロセス番号

DBのテーブルサイズの求め方

参考URL

mysqlshow -u root --status dbname > table_size.log

データ取り出し

mysql> SELECT * INTO OUTFILE '/tmp/test.txt' FROM yotei;
Query OK,5 rows affected (0.12 sec)

データ挿入

mysql> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE yotei;
Query OK,5 rows affected (0.02 sec)

外部キー

外部キー削除
alter table tablename drop foreign key tablename_lbfk_1

あるテーブルに関する外部キー制約を全て知る方法 4.1系

参考URLそのまま

地道に簡単に消していく方法
1. コンソールでdelete from table_name where id=xxx などを実行し、上記エラーが出る。

2. 以下のコマンドを実行する

show innodb status \G;

3. 色々表示される中に"LATEST FOREIGN KEY ERROR"という欄があり、下記のような感じの記述がある。

delete from TABLE_2 where ID=1
Foreign key constraint fails for table `scheme_1/TABLE_1`:
,
 CONSTRAINT `TABLE_1_ibfk_1` FOREIGN KEY (`COLUMN_1`)
REFERENCES `TABLE_2` (`COLUMN_2`) ON DELE
TE NO ACTION ON UPDATE NO ACTION

この場合TABLE_2テーブルのレコードを消そうとしたらscheme_1.TABLE_1.COLUMN_1に設定された外部キー制約に引っかかって失敗しており、それはTABLE_2.COLUMN_2カラムを参照しているという事が分かる。

4. 3で分かった原因レコード(この例だとTABLE_1テーブルのレコード)を消す。

5. また外部キーエラーが出たら2から繰り替えして、元を辿って順番に消す。
あるテーブルに関する外部キー制約を全て知る方法
show table status from スキーマ名 like 'テーブル名';
show create table テーブル名;

のどちらかで分かります。ただしこれは、そのテーブルから他のテーブルへの参照が分かるだけなので、逆にそのテーブルを外部キーとして設定している他のテーブルが分かりません。

そちらを知りたい場合は

show table status from スキーマ名;

として全テーブル分を出力し、結果をgrepします。えらいベタなのでもっとスマートなやり方がありそうなものですが、ちょっと調べた範囲では見つかりませんでした。
レプリケーションスキップ
$ mysql -u root 
> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
> START SLAVE SQL_THREAD;
MySQL 行番号表示
SET @i:=0;
SELECT
(@i:=@i+1) as row_number, a_col, b_col, c_col, d_col
FROM
test_table
ORDER BY
b_col;
外部キーなどテーブル情報を調べる
show create table tablename;
文字列の大文字小文字を判別する

参考ページそのまま。

――――――――――――――――――――――――――――――
MYSQLで何も指定しないでテーブルを作成した場合。”CASE IN SENSITIVE”
――――――――――――――――――――――――――――――
mysql> CREATE TABLE T_varchar (MOJI varchar(10) unique);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into T_varchar values(’varchar(10)’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into T_varchar values(’Varchar(10)’);
ERROR 1062 (23000): Duplicate entry ‘Varchar(10for key ‘MOJI’

mysql> select * from T_varchar;
+――――+
| MOJI |
+――――+
| varchar(10 |
+――――+
1 row in set (0.00 sec)

――――――――――――――――――――――――――――――
↓↓↓↓↓ MYSQLでbinaryを指定してテーブルを作成した場合  ”CASE SENSITIVE” ↓↓↓
――――――――――――――――――――――――――――――

mysql> CREATE TABLE T_binary (MOJI binary unique);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into T_binary values(’b');
Query OK, 1 row affected, 1 warning (0.14 sec)

mysql> insert into T_binary values(’b');
ERROR 1062 (23000): Duplicate entry ‘b’ for key ‘MOJI’
mysql> select * from T_binary;
+――+
| MOJI |
+――+
| b |
+――+
1 row in set (0.01 sec)

mysql>

mysql> insert into T_binary values(’B');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from T_binary;
+――+
| MOJI |
+――+
| B |
| b |
+――+
2 rows in set (0.00 sec)

参考URL

リストア時に外部参照キーのチェックを外す

参考URL

ダンプファイルの先頭行に以下の記述を追加します。
SET FOREIGN_KEY_CHECKS=0;

チューニング

SQLキャッシュを強制的に無効

パフォーマンス計測時などで、キャッシュなしのSQLリクエストを発行したいときがある。
SQL_NO_CACHEをはさむことでSQLキャッシュを強制的に無効にできる。

mysql> SELECT SQL_NO_CACHE column_a, column_b FROM table;