mysql


【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'に置き換える。すると制限にひっかからずに期待した結果が手に入る。

【mysql】「Lock wait timeout exceeded~」のエラー対処法

大して大きな処理のクエリでもないのにエラーが出た

----------------------------------------------------------
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
----------------------------------------------------------

実行中のスレッドを確認してみる。

実行したクエリ
----------------------------------------------------------
mysql> show processlist;
----------------------------------------------------------
mysql

とても長い時間、実行中のスレッドを発見。プロセスを強制終了する。

プロセスを終了させるクエリ
----------------------------------------------------------
mysql> kill 226948616;
----------------------------------------------------------

再び目的のクエリを実行すると、エラーも出ずにうまくいった。

【mysql】数値文字参照をUTF-8の文字に変換する

mysql等データベースに入った数値文字参照の文字列をUTF-8に変換する場合、mysql単体では無理なのでPHPを併用する。

数値文字参照を普通の文字に変換するPHP
------------------------------------------------------
$moji = mb_convert_encoding($moji, 'UTF-8', 'HTML-ENTITIES');
------------------------------------------------------
データベースから文字を取り出して、PHPで変換してUPDATEすればいい。

しかし文字列全てに対してmb_convert_encodingを行うと、希望しない文字まで変換してうまくいかないケースが出た。そこで数値文字参照の値にのみ変換をかけることにした。

文字列中の数値文字参照の値にのみ変換をかけるPHPサンプルコード
-------------------------------------------------------

//数値文字参照「&#~;」のみを拾う
preg_match_all ('/&#(.+?);/us',$string,$match);

foreach ($match[0] AS $moji1) {

echo "変換する文字:" . $moji1;

//数値文字参照をUTF-8に変換
$moji2 = mb_convert_encoding($moji1, 'UTF-8', 'HTML-ENTITIES');

//数値文字参照とUTF-8を置き換える
$string = str_ireplace($moji1,$moji2,$string);

}
-------------------------------------------------------

【mysql】改行を含むCSVファイルをインポートする方法

改行を含むCSVファイルのインポート方法。

CSVファイルの記述ルールは次の通り。

CSVのルール(一部)
------------------------------
LOAD DATA INFILEがデフォルトで対応している書式は、フィールド区切りがタブ、行末が改行。
値は基本、ダブルクォートで囲む。但し、「ダブルクォート(")」「カンマ(,)」「改行()」などの制御文字を含まない場合は「"」で囲まなくても可。
よって、値に改行を含む場合は、必ず「"」で囲む。

「"」のエスケープは「""」(連続したダブルクォート)。場合により「\"」のエスケープも可。
囲み文字と区切り文字の間には何も入れない。
×"値1", "値2" → ○"値1","値2" (×の例はカンマの左に半角スペースが入っている)
------------------------------
追加ルール1 → ファイルの一番最後に改行は入れない。改行を入れるとフィールドの区切り文字がデータと一緒に入ってしまう現象を確認。

つまり値を「"」で囲めば問題なく動作する。

実際のコマンド
---------------------------------------------
load data local infile "/home/username/www/text.csv" into table テーブル名 fields terminated by ',' enclosed by '"'
---------------------------------------------

「enclosed by」を指定して「"」が囲み文字であることを伝える。もちろんCSVファイルの値自体も「"」で囲むこと。

(※CSVファイルをZIPで圧縮のままload data local infileを実行すると、改行も区切りと認識してうまくいかないかも?実行するときは必ずCSVファイルの状態で。)

※ちなみにxamppをインストールしたwindowsのmysqlで実行する場合は「local」は不要。
CSVファイルのパスは、「¥」でなく「/」を使う
--------------------------------------------------------
load data infile 'C:/xampp/php/test.csv' into table テーブル名 fields terminated by '$' enclosed by '%'
--------------------------------------------------------

The used command is not allowed with this MySQL version」とエラーが出る場合は、「--enable-local-infile」をつけてmysqlサーバーに接続しなおすこと。詳しくはこちら

3 / 512345