mysql


新しいカラムを追加するmysqlクエリ

新しいカラムを既存カラムの後ろに追加するサンプルコード
----------------------------------------------------
ALTER TABLE テーブル名 ADD 追加するカラム名 定義
----------------------------------------------------
新しいカラムの場所指定がなければ、最後に追加される。

新しいカラムを既存カラムの先頭に追加するサンプルコード
----------------------------------------------------
ALTER TABLE テーブル名 ADD 追加するカラム名 定義 FIRST
----------------------------------------------------

既存カラムの後ろに新しいカラムを追加するサンプルコード
----------------------------------------------------
ALTER TABLE テーブル名 ADD 追加するカラム名 定義 AFTER カラム名
----------------------------------------------------

カラムの定義はこんな感じ
----------------------------------------------------
ALTER TABLE list ADD new_colunm text AFTER colunm1
----------------------------------------------------

カラムの定義その2
----------------------------------------------------
ALTER TABLE list ADD new_colunm varchar(20) AFTER colunm1
----------------------------------------------------

【mysql】CSVをload dataするときに気をつけること

mysqlにおいて、CSVをload dataするときに気をつけること。
うっかりミスを防ぐための自分用おぼえ書き。

1.CSVファイルの改行コードを必ず「LF(\n)」にする

「CR + LF」だと、行の区切りが正しく伝わらず、おかしなところでデータが分けられる。

また、データに改行を含まないときは、行の終わりを明示しておくとハマる可能性が減る。

----------------------------------
load data local infile "/home/username/www/text.csv" into table テーブル名 fields terminated by ',' enclosed by '"' lines terminated by '\n'
----------------------------------

2.NULLが怪しいと思ったら\Nに変えてみる

NULLは\Nで表すらしい。

3.Windows内のCSVファイルでもパスの区切りは「/」(スラッシュ)

通常Windowsのパスの区切りは\(バックスラッシュ)。
しかし、Windowsにインストールしたxamppのmysqlを利用する場合、CSVファイルのパス指定は「/」(スラッシュ)で行う。

コマンドプロンプトで指定
------------------------------------------------------
○ load data infile "C:/Users/username/Desktop/test.csv" into table ~
× load data infile "C:¥Users¥username¥Desktop¥test.csv" into table ~
------------------------------------------------------

MYSQLが遅かったとき、原因究明のため調べたこと

MYSQLが遅かったとき、原因究明のため調べたこと

ターミナルでMYSQLに接続して・・・

1.show processlistでQUERYの実行時間を調べる

2.innodb_thread_concurrencyを確認
InnoDB の処理を同時に実行できるスレッド数の上限値

コマンド
-------------------------------
show variables like '%thread%'
-------------------------------
上限なしの0なので問題なし

3.max_connectionsを確認
max_connectionsは同時接続数の最大値

コマンド
-------------------------------
show variables like '%conection%'
-------------------------------
151と出たので問題なし

4.mysqladminコマンドでMySQLサーバの状態を調べる

ディレクトリ移動(xamppの場合)
-------------------------------
C:\xampp\mysql\bin
-------------------------------

コマンド(パスワードなし)
-------------------------------
mysqladmin status
-------------------------------

コマンド(パスワードあり、xamppの場合)
-------------------------------
mysqladmin status -u ユーザー名 -p
-------------------------------

詳しく見る場合
-------------------------------
mysqladmin mysqladmin extended-status
-------------------------------

こちらを参考にした

エラーが出てることに気づいたので、
-------------------------------
lock wait timeout exceeded; try restarting transaction
-------------------------------

ロックの待ち時間を確認してみる。

コマンド
-------------------------------
show variables like '%timeout%'
-------------------------------

innodb_lock_wait_timeoutが50秒であることを確認。

この件は、テーブルにインデックスをつけることで問題は解決した。
こちら

ただの覚書でした。

【mysql】テーブルにインデックスを追加する方法

mysqlで、既存のテーブルにインデックスを追加する方法

------------------------------------------------
CREATE INDEX インデックス名 ON テーブル名(フィールド名);
------------------------------------------------

もしくは、
------------------------------------------------
ALTER TABLE テーブル名 ADD INDEX インデックス名(フィールド名);
------------------------------------------------

インデックス名は下記のように省略可。
------------------------------------------------
ALTER TABLE テーブル名 ADD INDEX (フィールド名);
------------------------------------------------
この場合、インデックスのフィールド名がそのままインデックス名となる。

TEXT型など一部の型にインデックスの追加を試みると、エラーになる場合がある。

エラーメッセージ
------------------------------------------------
BLOB/TEXT column 'text_field' used in key specification without a key length
------------------------------------------------

対処法。適当にキー長を明示する。
------------------------------------------------
ALTER TABLE テーブル名 ADD INDEX インデックス名(フィールド名(100));
------------------------------------------------

最後にインデックスできてるかを確認。

確認コマンド
------------------------------------------------
show index from テーブル名
------------------------------------------------

【MYSQL+PHP】トランザクション処理のサンプルコード

MYSQL + PHPでトランザクションを行うための記述例

トランザクションのサンプルコード
///////////////////////////////////////////////////////////////
//通常は、自動コミットが有効になっているのでクエリ入力後、すぐにコミット(データベースの更新処理)が行われる。
//トランザクション処理を行うため「SET AUTOCOMMIT=0」で自動コミットを無効する。

mysql_query("SET AUTOCOMMIT = 0"); //オートコミットを無効にする
mysql_query("begin"); //トランザクション開始

//----この間に複数の処理を記述----------------------------------

$sql1 = "UPDATE テーブル名1 WHERE ~; //処理1
mysql_query($up_sql1) OR die(mysql_error());

$sql2 = "DELETE FROM テーブル名2 WHERE ~" //処理2
mysql_query($up_sql2) OR die(mysql_error());

//----この間に複数の処理を記述 ここまで-------------------------


mysql_query("commit"); //ここで上記二つの処理が同時に実行される

///////////////////////////////////////////////////////////////

//commitがないので絶対にテーブルは更新されないサンプルコード
///////////////////////////////////////////////////////////////

mysql_query("SET AUTOCOMMIT = 0");
mysql_query("begin"); //トランザクション開始

$sql1 = "UPDATE テーブル名1 WHERE ~; //処理1
mysql_query($up_sql1) OR die(mysql_error());

$sql2 = "DELETE FROM テーブル名2 WHERE ~" //処理2
mysql_query($up_sql2) OR die(mysql_error());

mysql_query("rollback"); //ロールバックなので実行されない

///////////////////////////////////////////////////////////////

※仮にロールバックがなくても、コミットせずに接続を閉じても、MySQLはそのトランザクションをロールバックするよう。

コミットとロールバックを細かく振り分けるなら、次のような記述ができる。

コミットとロールバックを振り分けるサンプルコード
///////////////////////////////////////////////////////////////

$sql1 = "UPDATE テーブル名1 WHERE ~; //処理

$result = mysql_query($sql1);

if( $result === true ){

mysql_query("commit"); //確定
print "コミットしました";

}else{

mysql_query("rollback"); //巻き戻し
print "ロールバックしました";

}
///////////////////////////////////////////////////////////////

【mysql】重複したレコードのデータを取り出す

重複したレコードのデータを取り出すには、GROUP BY とHAVINGを使えばいい。

サンプルコード
----------------------------------------------------
SELECT カラム名 FROM テーブル名 GROUP BY カラム名 HAVING COUNT(*) >= 2;
----------------------------------------------------

HAVING句はグループ化が行われたデータに対して、条件を指定してデータの絞込みができる。サンプルコードでは、グループ化に指定したカラム名のうち、2つ以上あるものを取り出している。

※WHERE句とGROUP BY句を一緒に使った場合は、まずWHERE句よって条件に合うデータを抽出されて、その結果に対しGROUP BY句によってグループ化が行われる。HAVING句の場合と順序が逆なるということ。
※HAVEは捕まえる、選び取るの意。

複数のカラムが重複したレコードを取り出すサンプルコード
----------------------------------------------------
SELECT カラム名 FROM テーブル名 GROUP BY カラム名1,カラム名2 HAVING COUNT(*) >= 2;
----------------------------------------------------

【mysql】大容量のCSVファイルをload data infileで読み込めなかった時の対処法

mysqlで、load data infileを使いデータを読み込もうとしたがぜんぜん完了しない。しまいには、エラーが出た。

エラー内容
----------------------------------------------------------
ERROR 2013 (HY000): Lost connection to MySQL server during query
----------------------------------------------------------

4~6時間くらいか放っておいたら、切断がされたらしい。

実行環境
----------------------------------------------------------
Windows7にインストールしたxamppのmysql
----------------------------------------------------------

読み込んだCSVファイル
----------------------------------------------------------
'','だいこん[改行]
きゅうり[改行]
たくあん[改行]
レンコン[改行]
たまねぎ[改行]・・・・・' (←ここまで一件)
(こんなデータが1万2000件)
----------------------------------------------------------
2列でできたレコードが1万2000件ほど続くCSVファイル。行数にして8000万行以上(670MB)。
※1列目の空文字はプライマリキーなので読み込んだ後は一意の数字が入る。
※2列目は四文字くらいの単語の後に改行が入る。改行を数千回繰り返したものがひとつのレコード

xamppのコントロールパネルからエラーログを見ると、
----------------------------------------------------------
2014-02-04 05:12:30 3748 [Note] c:\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '5.6.14' socket: '' port: 3306 MySQL Community Server (GPL) ←実行開始時間
2014-02-04 11:19:23 3748 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 702845016. ← エラー発生時間
----------------------------------------------------------

「メモリが割り当てられない」みたいなことを言っている。
調べると、mysqlに使用するメモリは次の項目で設定できるらしい。

my.ini(旧my.cnf)の中身から抜粋(初期値)
----------------------------------------------------------
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
----------------------------------------------------------
項目の詳細は、こちらこちらが分かりやすい。ここも。

青字がメモリに関する箇所。
そこで次のように変更を加える。

----------------------------------------------------------

innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 256M
innodb_log_file_size = 512M
innodb_log_buffer_size = 768M

----------------------------------------------------------

mysqlをリブートして再び「load data infile」を実行。
するとできた。

----------------------------------------------------------
Query OK, 11827 rows affected, 11828 warnings (1 min 5.00 sec)
----------------------------------------------------------

所要時間およそ1分で読み込み完了。

/////////////////////////////////////////////////////////////
追記:
ただひとつのレコードに8000万の改行を含むデータを入れると別のエラーが出た。
----------------------------------------------------------
ERROR 5 (HY000): Out of memory (Needed 308285964 bytes)
----------------------------------------------------------
これについてはググっても設定ファイルいじっても解決せず。とりあえず保留。
/////////////////////////////////////////////////////////////

ちなみに行数を減らしたら次のような結果になった。

設定変更前
----------------------------------------------------------
300万行 44秒(改行LF + CRでエラー)
5秒 (LF 正常)
----------------------------------------------------------

↓↓↓

設定変更後
----------------------------------------------------------
300万行 2.5秒
----------------------------------------------------------

設定変更前
----------------------------------------------------------
1000万行 読み込めずエラー
----------------------------------------------------------

↓↓↓

設定変更後
----------------------------------------------------------
1000万行 7分(改行LF + CRでエラー)
1000万行 8秒(改行LF)
----------------------------------------------------------

さくら、poderosaでmysqlのデータ表示すると文字化けする

さくらのmysqlサーバーの文字コードはEUC-JP。

poderosaの文字コードもEUC-JPに合わせれば、きちんと見れる。

【mysql】テキストファイルに書かれたクエリを読み込んで実行する

テキストファイルに書いたmysqlのクエリ文を実行するには「source」コマンドを使う。

用意したファイル
------------------
test.txt
------------------

ファイルの中身
------------------
insert into テーブル名 (カラム名) VALUES ('内容1'),('内容2')
------------------

ターミナルでさくらのサーバーにログインして、mysqlに接続する。
データベースを選択して、「source」コマンドを実行する。

実際のコマンド
------------------
source /home/username/www/test.txt
------------------

【mysql】重複したカラムなら一行残して残りを削除するサブクエリ

column1に重複データがあれば、一行だけ残して削除するクエリを書いた。

--------------------------------------------------------
DELETE FROM table_name1 WHERE id in ( SELECT id FROM table_name1 GROUP BY column1 HAVING COUNT(*) >= 2 )
--------------------------------------------------------
(正確には、このクエリだと二行以上のデータに対し一行だけ削除していて、三行以上ダブっても一行しか消去してない)

しかしmysqlでクエリを実行してもエラーが出る

エラー内容
--------------------------------------------------------
You can't specify target table 'table_name1' for update in FROM clause
--------------------------------------------------------

日本語訳
--------------------------------------------------------
From句においてupdateのためのターゲットテーブル「table_name1」を指定できない
・clause(クローズ)→ 節。句。文節。文の一部分。
・specify → 指定できない。明示できない。
--------------------------------------------------------

どうもmysqlでは、あるテーブルに対してデータを追加や更新する場合、同じテーブルをサブクエリに使えないのらしい。(table_name1が重複)
--------------------------------------------------------
DELETE FROM table_name1 WHERE id in ( SELECT id FROM table_name1 GROUP BY column1 HAVING COUNT(*) >= 2 )
--------------------------------------------------------

そこで次のように書き換える。
--------------------------------------------------------
DELETE FROM table_name1 WHERE id in ( SELECT id FROM (SELECT id FROM table_name1 GROUP BY column1 HAVING COUNT(*) >= 2) AS x )
--------------------------------------------------------

サブクエリ内で作ったデータを一旦'X'に置き換える。すると制限にひっかからずに期待した結果が手に入る。

3 / 512345