【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;
----------------------------------------------------------

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

【PHP】simplexml_load_stringの使い方

シンプルにタグの間の文字を抜き出すサンプルコード
abcタグの間の文字を出力
----------------------------------------
<?php
//「$string = <<<XML」の下に改行は入れないこと!エラーになる
//行の先頭に半角スペースなどが入ることもないように(これコピペするとたぶん入る)
$string = <<<XML
<?xml version="1.0" ?>
<root>
<test>TEST1</test>
<test>TEST2</test>
<abc>xyz</abc>
</root>
XML;

$xml = simplexml_load_string($string);

//「->」(アロー演算子)で階層を下っていく
$content = $xml->abc;
print $content;
?>
----------------------------------------

testタグの間の文字を出力する
----------------------------------------
$content = $xml->test;
print $content;
----------------------------------------

但しこれだと最初のひとつしか出力されないので、すべて表示するには展開する。
----------------------------------------
foreach ($xml->test as $value) {
echo $value . "<br>";
}
----------------------------------------

【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サーバーに接続しなおすこと。詳しくはこちら

さくら共有サーバーでCSVファイルをインポートする方法

さくら共有サーバーでCSVファイルをインポートした際の覚書。
ファイルのインポートは「load data」を使う。

用意したCSVファイル、test.csv
---------------------------------
3,テストです
---------------------------------
インポート先のテーブルもフィールドは2つ。

1.SSHでサーバーにログインする

2.データベースサーバーにログインする

ログインした際のコマンド
---------------------------------
mysql -u ユーザー名 -h mysqlxxx.db.sakura.ne.jp -p --enable-local-infile
---------------------------------
大事なのは行末の「--enable-local-infile」でLOAD DATA LOCAL INFILE 文を使えるようにしておくこと。無いと「load data local」したとき、
「The used command is not allowed with this MySQL version」とエラーが出る

3.「load data」を実行してインポート

----------------------------------------
use データベース名 ←データベースを選択。「ユーザー名_データベース名」となっているのを忘れないこと。

load data local infile "/home/username/www/test.csv" into table テーブル名 fields terminated by ',';
----------------------------------------
「LOCAL」をつけることで、FILE権限なしで、ローカルファイルの読み込みが可能になる。「fields terminated by」はフィールド区切り文字の指定。ちなみに囲み文字の指定は「OPTIONALLY ENCLOSED BY」で行う。
行の終わりは「lines terminated by '\n'」で明示できる。

【mysql】さくら共有サーバーで特定のカラムだけダンプ(エクスポート)する方法

通常、mysqlで特定のカラムだけダンプ(エクスポート)場合、INTO OUTFILEを使えばいいが、さくら共有サーバーでは権限の都合で使用不可。

そこでとった行動の覚書。

作業1.欲しいカラムだけをコピーして新しいテーブルを作る。

クエリ
-------------------------------------------------
CREATE TABLE copy_table AS SELECT column1 FROM main_table
-------------------------------------------------

作業2.新しく作ったテーブルをダンプ(エクスポート)。不要になったテーブルは削除。

さくら共用サーバーでmysqldumpを使う方法

さくら共用サーバーでmysqldumpを使ったときの覚書。

1.SSHでサーバーにログインする。

2.データベースサーバーにはログインせずに、mysqldumpを実行。ファイルはカレントディレクトリに保存されるので、必要に応じてcdする。

//入力するコマンド
----------------------------------------------------------------
mysqldump -u ユーザー名 -h データベースサーバー名(mysqlxxx.db.sakura.ne.jp) データベース名 -p > mysql.dump(←保存ファイル名)
----------------------------------------------------------------

ただこの方法では自分がしたかった、特定のカラムだけ取り出すことはできなかった。INTO OUTFILEを使えばできるようだが、さくら共有サーバーでは権限なくて無理。

エンコードされたURLが%2Fや%5Cが含むと404エラーになる場合の回避法

URL変更など、mod_rewrite でURLを渡す時、「%2F」や「%5C」が含まれると404エラーが返ってくる。Apacheのデフォルト設定である。

「%2F」は「/(スラッシュ)を、%5Cは「\(バックスラッシュ)」をエンコードしたもの。
これらの文字を含んだURLで404エラーなる場合は、AllowEncodedSlashesを設定することでURLの使用可否を変更する。

httpd.confに追加する(.htaccessでは不可)
---------------------------
AllowEncodedSlashes on
---------------------------
エンコードされた「/」や「\」を含むURLの使用が許可される。

レンタルサーバーなどでhttpd.confをいじれない場合は、「/」や「\」を二重にエンコードすることでとりあえず回避できる。

「/」を二重にエンコードする例
---------------------------
$url = urlencode($url); //一回目のエンコード
$url = str_ireplace("%2f","%252f",$url); //二回目。スラッシュだけエンコード値に置換。
---------------------------

PHPでURLがリンク切れか調べる【get_headers】

PHPでURLがリンク切れか調べるには、get_headers関数でHTTPヘッダを取得する。(PHP5で追加された)

リンク切れ(404エラー)を調べるサンプルコード
----------------------------------------------
$head = get_headers($url); // HTTP リクエストに対するヘッダを取得する

$kekka = $head[0]; //添字0がステータスコード

echo $kekka;
//404エラーの場合「HTTP/1.1 404 Not Found」と表示される
----------------------------------------------

404エラー時にファイル書き込む場合
----------------------------------------------
if(stristr($info[0],"404")) {

//ファイルに書き込む
$fp = fopen("404list.txt", "a"); //aは追記
fwrite($fp, "404エラーです");
fclose($fp);

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

但し、URLに半角スペースが使われていると、リンクがあっても404エラーが返ってくる。これを回避するためには、rawurlencodeで半角スペースを「%20」に置き換える必要がある。
urlencodeを使うと半角スペースは「+」に置き換わるが、これだと404エラーは回避できなかった。