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を使えばできるようだが、さくら共有サーバーでは権限なくて無理。

【mysql】正規表現でひらがなだけの文字列を取り出す

全部ひらがなで構成された文字列だけを抜き出そうと、下記のSQLを実行した。

SELECT field1 FROM table1 WHERE field1 REGEXP '^[ぁ-ん]+$'

しかし文字列の中にひらがなの「む」がはいってない文字列だけマッチしなかった。
文字コードの設定によって起こる現象らしい。

そこで次のように変更↓↓↓

SELECT field1 FROM table1 WHERE field1 REGEXP '^[ぁ-んむ]+$'

「む」を付け足して解決。

【mysql】特定の長さの文字列だけをselectで取り出す

フィールドの中の、特定の長さのレコードだけを取り出す場合の記述

SELECT * FROM table1 WHERE LENGTH(field1) > 60

mysqlの正規表現で括弧()をマッチさせる

mysqlの正規表現で括弧()をマッチさせるには、エスケープシーケンスを連続して記述する。(\\)

下記は 行頭が (11) の文字列にマッチさせる場合。

REGEXP '^\\(11\\)'

エスケープシーケンス(\)が1つの場合はMySQLに対するエスケープとみなされる。
正規表現としてのエスケープするために、2つ並べている。

PHPファイルの中にsql文を記述する場合は、「\」が四つ並んだりする

$sql = "SELECT field FROM table WHERE field REGEXP '^\\\\(11\\\\)'";

mysqlで正規表現を使って置換する方法

mysqlは正規表現のREGEXP関数、置換のREPLACE関数があるが、同時に使えない。

なのでPHPを組み合わせて利用する方法を取る。

次のPHPスクリプトでは、field1 に入っている文字列が「;」で終わっていた場合に、その部分だけを削除している。

--------------------------------------------------------------------------
<?php
$pattern = ";$"; //正規表現のパターン

//mysqlの正規表現を使ってUPDATEの対象になるレコードを取り出す。
$sql = "SELECT field1 FROM table1 WHERE field1 REGEXP '" . $pattern . "'";
$result = mysql_query($sql) or die(mysql_error()); //sqlを実行

//全て展開
while ($item = mysql_fetch_array($result)) {
$in_data = $item['field1'];
$out_data = preg_replace( "/" . $pattern . "/", "", $in_data); //PHPで置換
$new_sql = "UPDATE table1 SET field1 = '".$out_data."' WHERE field1 = '".$in_data."'"; //置換した文字列をUPDATEするsql文
mysql_query($new_sql) or die(mysql_error()); //sqlを実行
}
?>
--------------------------------------------------------------------------

このファイルをサーバーにアップロードして、ブラウザでアクセスすればPHPおよび、mysqlが実行される。

【mysql】特定のフィールドで重複しているレコードを削除する

たとえば"field1"でレコードが重複しているので、一つを残してその他を削除したいときの手順。

1.field1をグループ化して、重複データを1つにまとめる。その状態のテーブルをコピー。

//グループ化してcopy_tableという名前のテーブルを作った。
CREATE TABLE copy_table as SELECT * FROM main_table GROUP BY field1

2.メインテーブルを削除。(くれぐれも自己責任で!)
DROP TABLE main_table

3.コピーしたテーブルをメインテーブルにリネーム。
ALTER TABLE copy_table RENAME TO main_table

【mysql】 カラムに文字列を追記する方法

すでに内容のあるカラムに文字列を追記するには、「concat」を使う。
以下のとおり。

UPDATE テーブル名 SET 追加先のカラム = concat(追加先のカラム, '追加したい文字列');

ちなみに、追加先のカラムが「null」の状態だと追記されません。

【mysql】複数のテーブルのデータをinsertで一つのテーブルにまとめる

複数のテーブルのデータを一つのテーブルにinsertで追加、まとめるsql

INSERT INTO table_name (column1,column2) SELECT column1,column2 from table_name2;

データが追加される側のカラムとデータを参照するカラムの構造は同じにしてください。

4 / 512345