ウェブインコ

インコの技術メモ

MySQL

■よく使うコマンド
C:\>mysql -u root -p ログイン。
パスワード設定してなければEnter。
show databases; データベース一覧を見る。
show tables from test_db; テーブル一覧を見る。
use test_db; データベースを掴む。
show tables; 掴むとfrom以下を省略できる。。
show columns from list_t; テーブルの定義を見る。
desc list_t; テーブルの定義を見る。上と同じ。
select * from list_t; レコードを全部見てみる。
select * from list_t where coach_id=14; レコードを見る。
insert list_t set coach_serial=kwt00002; レコードを追加する。
update list_t set coach_serial=kwt00002 where coach_id=14; レコードを更新する。
delete from list_t where id=4; レコードの削除。
delete from serial where serial between a and 1000;
create database test2_db; データベース作成。
drop database test_db; データベース削除。
ALTER TABLE list_t ADD test_c INT(11) AFTER test2_c; ■カラムの追加。
 文字列で大文字小文字の区別をつけるとき
 ALTER TABLE list_t ADD list_c VARCHAR(20) COLLATE 'utf8_bin' AFTER test2_c;
ALTER TABLE m_inventory MODIFY arrival_date DATE NOT NULL DEFAULT 0; ■カラム定義の変更。
ALTER TABLE list_t CHANGE test_c test_cc INT; ■カラム名の変更。
ALTER TABLE m_order_header DROP serial_pair; ■カラムの削除。
alter table tablename rename to newtablename; ■テーブル名の変更
drop table テーブル名; テーブルの削除。
OPTIMIZE TABLE テーブル名;insert delete を繰り返し過ぎるテーブルのスッキリ。
exit; 終了
C:\>net stop mysql MySQLサービス終了
C:\>net start mysql MySQLサービス開始
テーブル追加
CREATE TABLE t_inventory_record (serial INT(11) NOT NULL DEFAULT 0, upd_date DATE NOT NULL DEFAULT 0, status INT(1) NOT NULL DEFAULT 0, stock INT(11) NOT NULL DEFAULT 0, arrival_date DATE NOT NULL DEFAULT 0, before_status INT(1) NOT NULL DEFAULT 0, before_stock INT(11) NOT NULL DEFAULT 0, before_arrival_date DATE NOT NULL DEFAULT 0, before_lastup_date DATE NOT NULL DEFAULT 0);
CREATE TABLE access_log_r (campaign_id INT(11) NOT NULL, log_date date NOT NULL,log_kind text NOT NULL,raw_log longblob NOT NULL,update_date datetime NOT NULL,insert_date datetime NOT NULL);
プライマリーキーの追加
alter table access_log_r add primary key(campaign_id,log_date);
プライマリーキーに変更
ALTER TABLE categories MODIFY id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
テーブル作成レコード作成の例
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
loginid VARCHAR(50),
password VARCHAR(50),
created DATETIME DEFAULT NULL,
updated DATETIME DEFAULT NULL
);
INSERT INTO users (name,loginid,password,created,updated)
VALUES ('テストマン', 'aaaa', 'aaaa', NOW(), NOW());
CREATE TABLE new_hoge LIKE hoge; テーブルのスキーマをコピー
INSERT INTO new_hoge SELECT * FROM hoge; テーブルのデータを挿入

■スキーマだけダンプ
mysqldump hoge --no-data > hoge.dump

■特定のテーブルだけダンプ
mysqldump -u root -p databasename tablename > "tablename.dump"

■それを違うDBにコピー
mysql -u root -p databasename2 < "tablename.dump"

■DB構築(削除→作成→復旧)
プロンプト
mysqldump -u root -p ec > "ec508.dump"
MySQL
drop database ec;
create database ec;
プロンプト
mysql -u root -p ec < "ec511.dump"

■オートインクリメントごと削除
TRUNCATE tablename;

■前から10文字
SELECT id, name, LEFT(address, 10) FROM users;

■ジョイン
FROM model1 A LEFT JOIN model2 B ON A.id = B.a_id  :左(A)全部
FROM model1 A RIGHT JOIN model2 B ON A.id = B.a_id :右(B)全部
FROM model1 A INNER JOIN model2 B ON A.id = B.a_id :両方が叶うもののみ
例1)
SELECT hoge1.id, hoge1.hage, hoge2.hage_r FROM hoge1 LEFT JOIN hoge2 ON hoge1.id = hoge2.hoge1_id WHERE hoge1.hage IS NULL AND hoge2.hage_r IS NOT NULL;
例2)
ただし、以下のように右でしか条件を選ばない&左∈右ならば RIGHT でも INNER でも結果は同じです。
当たり前と言えば当たり前ですが。
SELECT COUNT(A.name) FROM dtb_products A INNER JOIN dtb_products_class B ON A.product_id = B.product_id WHERE B.stock = 0;
SELECT COUNT(A.name) FROM dtb_products A RIGHT JOIN dtb_products_class B ON A.product_id = B.product_id WHERE B.stock = 0;

■2つのテーブルで片方にだけあるものを抽出
SELECT * FROM table_a WHERE NOT EXISTS(SELECT * FROM table_b WHERE table_b.key=table_a.key);

■あるカラムを除いた SELECT文
例)
DB:hogedb
テーブル:users
カラム:id,name,kana,....,status,updated,created

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), ',status,updated,created', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND TABLE_SCHEMA = 'hogedb'), ' FROM users');
PREPARE statement1 FROM @sql;
EXECUTE statement1;

statement1 は任意の文字列。exitするまで覚えてくれてるので、2回目以降は EXECUTE statement1; だけでOK。

■テーブル名にAとかBとか
SELECT A.* FROM admin A, license B WHERE B.paid_date LIKE "2009%" AND A.id = B.admin_id GROUP BY A.id;

■テーブル3つで連結
SELECT A.name AS user_name, C.name AS image_name FROM users A JOIN users8images B ON A.id = B.user_id JOIN images C ON B.item_id = C.id WHERE A.id=1;
SELECT A.name AS user_name, C.name AS image_name
FROM users A
JOIN users8images B ON A.id = B.user_id
JOIN images C ON B.item_id = C.id
WHERE A.id=1;

■DBが他のサーバーにある場合
mysql -hhoge.host.jp -u hogeuser -phogepasswd hogedatabase
mysqldump -hhoge.host.jp -u hogeuser -phogepasswd hogedatabase > "hoge.dump"

■部分的に重複しているレコード
作成日の年月別で調べたいときとか
select distinct left(created,7) from reports;
+-----------------+
| left(created,7) |
+-----------------+
| 2011-09 |
| 2011-10 |
| 2011-11 |
+-----------------+

■echoを使った例
echo "select * from m_table"|mysql -u root pパスワード dbname;
echo "select * from m_table"|mysql -u root pパスワード dbname > result.txt;

■バージョンを調べる
mysqladmin version

■自動起動の設定方法
# chkconfig mysqld on
# chkconfig --list mysqld
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
2?5がonであることを確認。

■大量データーを扱うときの注意
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
対象データーが大きくなると、↑のエラーがぼちぼち出ます。
設定を変えることによってスルーすることも出来ますが根本的な解決にはならないので、以下。
・JOIN しすぎると返ってこなくなります。
 INNER JOIN とかもってのほか。
 ソースが多少ダサくなってもSQL文を数回に分けたり、有る程度絞ってからループ文で回すなどすると100倍早くなります。というか処理してくれます。
・WHERE テーブル名 IN (v1,v2,v3,......) も危険。
 WHERE IN は結構早くて重宝するのですが、引数の部分で、対象のデーターが巨大だったりで、引数が多くなったりすると処理してくれなくなります。
 ここは反対に括弧の中の v1,v2,v3,...... の部分を SELECT ID FROM ? に置き換えてやると帰ってくるようになります。実質的には同じ内容でもSELECT文を書くのが良いみたいです。

■複数のデータベースを削除する
以下の様なテキストファイルでも作って cat drop.sql | mysql -u root -p
「drop.sql」
drop database if exists masu1;
drop database if exists masu2;
drop database if exists masu3;
drop database if exists masu4;
drop database if exists masu5;

■table2 に存在しない id 値を持つすべてのレコード(table2 に対応するレコードがないすべてのレコード)が table1 で検索される。
mysql> SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

■重複しているものを1つにまとめて表示
SELECT * FROM t_account GROUP BY name1_1 HAVING COUNT(name1_1)>1;

■日本語が入らない時
/etc/my.conf に以下を書き足して /etc/init.d/mysqld restart
[client]
default-character-set=ujis
[mysql]
default-character-set=ujis

■文字の置換え
TRANSLATEは、検索文字列式と置換文字列式を一文字づつ対応させて置き換えます。
REPLACEは、単語単位の置換えです。
例:
 文字列式=abcdefdcba
 検索文字列式=abcd
 置換文字式=置換文字
TRANSLATE(文字列式, 検索文字列式, 置換文字列式) → 置換文字ef字文換置
REPLACE(文字列式, 検索文字列式, 置換文字列式)  → 置換文字efdcba

■文字連結
update contents set meta=concat('aaaaa',カラム名);

■検索条件文
voice_? と index 以外。
select meta from contents where not(filename like "voice_%") and not(filename = 'index');

■IDをふりなおす(+値)
update image set id=id+値;

■IDをふりなおす(1から)
mysql> ALTER TABLE my_table DROP id;
mysql> ALTER TABLE my_table ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

■MySQL最大値
select max(id) from image;

■csvファイルに出力
select A.iNumber,A.szTitle,A.szBody,B.szTitle,B.szBody from bbs_oya A,bbs_ko B where A.iNumber=B.iOya_number order by A.iNumber into outfile '/home/bbs.csv' fields terminated by ',';

■メモ
update const set disp='当選者数/応募者数' where group_code='data_kind' and item_id='3';

■keyカラムを指定しないとdeleteできないようにmy.cnfにsafe-updatesと書く
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
safe-updates
或いは起動時に --safe-updates を付ける
mysql -u root -p --safe-updates

■safe-updatesを無視する
my.cnf に書いてある場合は --safe-updates=0 をつける
mysql -u root -p --safe-updates=0

■my.cnf の作成
以下のところからコピーして置きます。
cp /usr/local/mysql/share/mysql/my-small.cnf /etc/my.cnf

■safe-updates時のレコードのdelete
delete from m_inventory where serial>0 limit100;

■圧縮の方法
tar zcvf tabi.tgz patchfiles
左圧縮後ファイル名 右圧縮されるファイル

■解凍の方法
tar zxf 圧縮ファイル名

■ユーザ一覧
mysql -u root -p
use mysql;
select * from user;

■ユーザ追加
(全権限)
GRANT ALL ON *.* TO hogeuser@localhost IDENTIFIED BY 'hogepw';

(権限制限)
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO hogeuser@localhost IDENTIFIED BY 'hogepw';

(特定のDBしか見えないユーザー)
GRANT ALL ON hogedb.* to hogeuser@localhost;
FLUSH PRIVILEGES;
SET PASSWORD FOR hogeuser@localhost=password('hogepw');

(ver 5.7)
CREATE USER 'hogeuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'hogepw';
GRANT ALL ON hogedb.* TO hogeuser@localhost;
FLUSH PRIVILEGES;

※hogedb,hogeuser,hogepwは任意の文字列

■ユーザ削除
DELETE FROM mysql.user WHERE user='username' and host='hostname';
FLUSH PRIVILEGES;

(ver 5.7)
DROP USER user@localhost;

■文字コード変更
/etc/my.cnf
default-character-set ujis
とかにする。ujis は EUC Japanese のこと。
コマンドラインではこう。
set names ujis;

■現在の文字コードの状態を調べる
1.現在の設定を表示
status
2.文字コード関係(ぽい)変数を表示
show variables like "char%";

■文字化け、違う環境にて
$dbh->do('set character set ujis') がバージョンによって要ったり要らなかったりなので
my $dbh = DBI->connect( get_property('data_source'), get_property('db_user'), get_property('db_password') );
$dbh->do('set character set ujis') if((get_property('mysql_version') || '5')=~ /^4/o);
return $dbh;

■localPCのMySQL
/usr/local/mysql/bin/mysqld_safe --user=mysql &
/usr/local/mysql/bin/mysqladmin -u root -p shutdown

■データベースからデータだけ抜く
grep INSERT db20070409.dump > db20070409.insert.dump

■ログの設定
/etc/my.cnf
log=/var/log/mysql.log
 > /var/log/mysql.log
/etc/init.d/mysqld restart

■シンボリックリンクを貼る
ln -s /usr/local/mysql/bin/mysqldump mysqldump

■リード例
select * from $B where name like '%$name%' and del_flg='active' order by disp_order,serial"
like あいまい検索 %%ではさむと部分検索になる。
and 条件追加。
order by ソートのキー。複数指定可能。後ろにDESCをつけると降順。


■my.conf
上コマンドライン用のツーツの設定
下PerlやPHPからアクセスするときに必要なので追加して下さい。
[mysql]
default-character-set=sjis

[mysqld]
default-character-set=sjis
old-passwords

(2006.1.11 書き忘れててもブラウザ(Perl)から見れた(^-^;)


■SELECT LAST_INSERT_ID();
インサートしたそのIDを取得したい時に使われます。
良く理解せずに使っていたので確認しましたよというメモ。

【疑問】
(1) INSERT INTO users SET name = 'testman';
してからの、
(2) SELECT LAST_INSERT_ID();

(2)で最後にインサートされた ID を取得しているわけだけれど、万が一、万が一だけど(1)と(2)の間に他の人が INSERT したらどうなるの。
つまりこういうこと、

Aさんの処理で、INSERT INTO users SET name = 'testman'; ID に 1 が自動採番。
Bさんの処理で、INSERT INTO users SET name = 'testgal'; ID に 2 が自動採番。
Aさんの処理で、SELECT LAST_INSERT_ID(); ID = 2 が返ってくる。
Aさん的には 1 が欲しかったのに、横殴りのせいで 2 を取得してしまう。

こんな絶妙なタイミングで入れ子になること自体がそもそもないっ、とも思えますが可能性としては無くは無い。
これがもし本当に怒ってしまったら、会員登録した瞬間別人の内容が見えてしまったりするかもしれない、怖い。

しかし、安心してください。1 が返ってきますよ。

SELECT LAST_INSERT_ID() は自分が行った分しか管理していません。
もっと厳密に言うと、
接続ごと(DBにコネクトしてからクローズするまでの間)に、自分が行ったオートインクリメントの値だけを保持し呼び出されれば返す
ということです。
なので、INSERT してから SELECT LAST_INSERT_ID の間に、他の人が何人割り込まれても大丈夫です。
(ロールバックが起こったり、入れ子になっている瞬間は 自分が確保した ID がとびます)

注意1
上記のことから、以下のようにすると 0 が返ってきてしまうので注意してください。

Perl の例
my $dbh = DBI->connect($dsn, $dbUser, $dbPass); # 接続
my $query = "INSERT INTO users SET name = 'testman'";
$rows = $dbh->do($query); # 挿入
$dbh->disconnect; # 切断
my $dbh = DBI->connect($dsn, $dbUser, $dbPass); # 接続
my $rows = $dbh->selectall_arrayref("SELECT LAST_INSERT_ID()", { Columns => {} });
print Dumper $rows->[0]; # 最後のオートインクリメント表示
0 になります。
切断するとリセットされます。


■ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)
ある日突然繋がらなくなりました。
_|?|○
my.iniを旧に戻しても繋がりませんでした。
my.iniを旧に戻してPCを再起動したら繋がりました。
このあとmy.iniを元に戻して見たら見えました。PC再起動はしていません。
b

# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=sjis
ここだけshisにして他のdefault-character-setを全てeucにしたら繋がるし化けません


■データベースの保存復元のLinux→Windowsで日本語が文字化け
いろいろ試したけどutf8で半分だけ見える。(-_-;
linuxでダンプとると強制的にutf8になる模様。バージョンによっては以下の対応で直
ることがあるらしいが、今の環境ではダメでした。binalyが指定できない。

問題になったのは、2の部分で文字化けに悩まされてました。
結果的には、mysqldumpの文字コードの問題(内部で強制的にutf-8に変換してしまう)のためで、
my.cnfの[mysqldump]のdefault-character-setをbinaryにしたら解決しました。
/etc/my.cnf に下記行を追加
[mysqldump]
default-character-set=binary
ちなみにcharacter-set = ujis等に指定しても文字化けするのでbinary以外はだめみたいです。
あとは、Linux上バックアップ
$ mysqldump -u root -pパスワード DB名 > backup.sql
Windows上でデータベースを作成してからリストア
mysql > create database データベース名
$ mysql -u root -pパスワード DB名 < backup.sql


■データベースの保存と復元
Windowsのコマンドプロンプトから。
保存
C:\>mysqldump -u root -p test_db > "test_db.dump"
復元
C:\>mysql -u root -p test_db < "c:\test_db.dump"
違うMySQLへの復元するときはあらかじめ受け皿を作っておく。
C:\mysql -u root -p
mysql> create database test_db;
mysql> exit;
削除
また、既存のDBの中にテーブルを復旧させる場合は、同じ名前のテーブルがすでにその
DBにあると復旧は行われませんので、そのテーブルを削除しておきます。
mysql> drop database test_db;