[MySQLデータ移行]エクスポートとインポート

$ uname -a
Linux localhost 4.14.214-160.339.amzn2.x86_64 #1 SMP Sun Jan 10 05:53:05 UTC 2021 x86_64 x86_64 x86_64 GNU/Linu
$ mysql –version
mysql Ver 8.0.23 for Linux on x86_64 (MySQL Community Server – GPL)

$ mysql -u root -p
mysql> show databases;
mysql> use nonemail
mysql> show tables;
+————————+
| Tables_in_nonemail |
+————————+
| failed_jobs |
| migrations |
| password_resets |
| personal_access_tokens |
| sessions |
| users |
+————————+
mysql> select * from users;
-> usersテーブルに4件データが入っています。

### mysqlのデータエクスポート
$ mysqldump -u root -p –opt nonemail > database_name.sql
$ ls
database_name.sql

dumpファイルの中身
-> sql文が入っている

### mysqlのdumpファイルからデータインポート
$ ls
database_name.sql
$ mysql -u root -p newdatabase < database_name.sql Enter password: ERROR 1049 (42000): Unknown database 'newdatabase' -> ん? dbを作ってからでないとダメ??

mysql> create database newdatabase;
Query OK, 1 row affected (0.01 sec)
$ mysql -u root -p newdatabase < database_name.sql Enter password: mysql> use newdatabase;
mysql> show tables;
+————————+
| Tables_in_newdatabase |
+————————+
| failed_jobs |
| migrations |
| password_resets |
| personal_access_tokens |
| sessions |
| users |
+————————+
6 rows in set (0.00 sec)
mysql> select * from users;

ちゃんとデータも入ってます^^

なるほど、mysqlのデータ移行の方法は理解した。

[MySQL8.0.22] 機種依存文字の保存

update users set name=”アイウエオ、カキクケコ” where id=3;

| 3 | NULL | アイウエオ、カキクケコ |

update users set name=”①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳:㍉㍍㌔㌘㌧㌦㍑㌫㌢:㍻㍼㍽㍾:ⅠⅡⅢⅣⅤⅥⅦⅧⅨⅩ” where id=3;
| 3 | NULL | ①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳:㍉㍍㌔㌘㌧㌦㍑㌫㌢:㍻㍼㍽㍾:

update users set name=”〃 仝 ゝ ゞ 々 〆 ヾ ― ‐ / 〇 ヽ _  ̄ ¨ ` ´ ゜ ゛ \ § ^ ≫ ¬ ⇒ ⇔ ∀ ∃ ∠ ⊥ ⌒ ∂ ∇ ≡ ∨ ≪ † √ ∽ ∝ ∵ ∫ ∬ Å ‰ ♯ ♭ ♪ ‡ ~ ′ ≒ × ∥ ∧ | … ± ÷ ≠ ≦ ≧ ∞ ∴ ♂ ♀ ∪ ‥ ° ⊃ ⊂ ⊇ ∩ ⊆ ∋ ∈ 〓 〒 ※ ″” where id=3;
| 3 | NULL | 〃 仝 ゝ ゞ 々 〆 ヾ ― ‐ / 〇 ヽ _  ̄ ¨ ` ´ ゜ ゛ \ § ^ ≫ ¬ ⇒ ⇔ ∀ ∃ ∠ ⊥ ⌒ ∂ ∇ ≡ ∨ ≪ † √ ∽ ∝ ∵ ∫ ∬ Å ‰ ♯ ♭ ♪ ‡ ~ ′ ≒ × ∥ ∧ | … ± ÷ ≠ ≦ ≧ ∞ ∴ ♂ ♀ ∪ ‥ ° ⊃ ⊂ ⊇ ∩ ⊆ ∋ ∈ 〓 〒 ※ ″

全部いけるんか。すげえ。
続いて、PDO

$name = "〃 仝 ゝ ゞ 々 〆 ヾ ― ‐ / 〇 ヽ _  ̄ ¨ ` ´ ゜ ゛ \ § ^ ≫ ¬ ⇒ ⇔ ∀ ∃ ∠ ⊥ ⌒ ∂ ∇ ≡ ∨ ≪ † √ ∽ ∝ ∵ ∫ ∬ Å ‰ ♯ ♭ ♪ ‡ ~ ′ ≒ × ∥ ∧ | … ± ÷ ≠ ≦ ≧ ∞ ∴ ♂ ♀ ∪ ‥ ° ⊃ ⊂ ⊇ ∩ ⊆ ∋ ∈ 〓 〒 ※ ″";
$name = mb_convert_encoding($name, "UTF-8", "utf-8");
$stmt = $pdo->prepare("INSERT INTO test1(name) VALUES(:name)");
$stmt->bindParam(':name', $name,PDO::PARAM_STR);
$stmt->execute();

mysql> select * from test1;
+—-+————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| id | name |
+—-+————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| 1 | test |
| 2 | アイウエオ、カキクケコ |
| 3 | ①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳:㍉㍍㌔㌘㌧㌦㍑㌫㌢:㍻㍼㍽㍾:ⅠⅡⅢⅣⅤⅥⅦⅧⅨⅩ |
| 4 | 〃 仝 ゝ ゞ 々 〆 ヾ ― ‐ / 〇 ヽ _  ̄ ¨ ` ´ ゜ ゛ \ § ^ ≫ ¬ ⇒ ⇔ ∀ ∃ ∠ ⊥ ⌒ ∂ ∇ ≡ ∨ ≪ † √ ∽ ∝ ∵ ∫ ∬ Å ‰ ♯ ♭ ♪ ‡ ~ ′ ≒ × ∥ ∧ | … ± ÷ ≠ ≦ ≧ ∞ ∴ ♂ ♀ ∪ ‥ ° ⊃ ⊂ ⊇ ∩ ⊆ ∋ ∈ 〓 〒 ※ ″ |
+—-+————————————————————————————————————————————————————————————————————————————————————————————————————————-+

ちょっと整理する必要があるな。

[MySQL8.0.22] Oracleのデータ型(NUMBERとDATE)を挿入する

OracleのNUMBER(16,4)とDATEをMySQLに挿入したい。

取り敢えずOracle側のテストデータとして以下を用意する。
2021/01/07 10:00:00
1500.0000

mysqlのCreate table

create table oracle_test(
	id int unsigned auto_increment primary key,
	price4 decimal(10,4),
	price2 decimal(10,2),
	datetime_at datetime,
	timestamp_at timestamp
);

mysql> describe oracle_test;
+————–+—————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————–+—————+——+—–+———+—————-+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| price4 | decimal(10,4) | YES | | NULL | |
| price2 | decimal(10,2) | YES | | NULL | |
| datetime_at | datetime | YES | | NULL | |
| timestamp_at | timestamp | YES | | NULL | |
+————–+—————+——+—–+———+—————-+
5 rows in set (0.00 sec)

insert into oracle_test(price4) value("1500.0000");
insert into oracle_test(price2) value("1500.0000");
insert into oracle_test(datetime_at) value("2021/01/07 10:00:00");
insert into oracle_test(timestamp_at) value("2021/01/07 10:00:00");

mysql> select * from oracle_test;
+—-+———–+———+———————+———————+
| id | price4 | price2 | datetime_at | timestamp_at |
+—-+———–+———+———————+———————+
| 1 | 1500.0000 | NULL | NULL | NULL |
| 2 | NULL | 1500.00 | NULL | NULL |
| 3 | NULL | NULL | 2021-01-07 10:00:00 | NULL |
| 4 | NULL | NULL | NULL | 2021-01-07 10:00:00 |
+—-+———–+———+———————+———————+
4 rows in set (0.00 sec)

insert into oracle_test(price2) value(“1500.0050”);
| 5 | NULL | 1500.01 | NULL | NULL |

なるほど、mysql側で勝手に変換してくれんのか。
凄いけど、型づけ言語の考え方からいくと、型が完全に一致してないのに入るのは気持ち悪いな。
まあ、取り敢えず良しとしよう。

[MySQL8.0.22] updateでTruncated incorrect DOUBLE value

値を慌ててupdateしたい。

update users set name=’営業所’ and tel=’03-1234-5678′ where id=1;
-> ERROR 1292 (22007): Truncated incorrect DOUBLE value: ‘営業所’

なんやと!!!

update users set name=’営業所’, tel=’03-1234-5678′ where id=1;

andではなく、カンマ(,)で区切るのが正解なんだと。。
ビックリするぜ。

[MySQL8.0.22]改行テキストをCSV・PDOでinsertしたい

mysql> select version();
+———–+
| version() |
+———–+
| 8.0.22 |
+———–+
1 row in set (0.00 sec)

まず適当にテーブルを作ります。

create table news(
	id int auto_increment PRIMARY KEY, 
	name varchar(20),
	body text
);

mysql> describe news;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| body  | text        | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

insert intoでは\nで改行になる。

INSERT INTO news(name, body) VALUE ('NHK', '公務員に冬のボーナス 国家公務員平均約65万円 3年連続の減少');

INSERT INTO news(name, body) VALUE ('NHK', '公務員に冬のボーナス\n国家公務員平均約65万円 3年連続の減少');

### pdo
pdoで入れる際は、\r\nをつける。

$sql = 'INSERT INTO news (name, body) VALUE (:name, :body)';
$prepare = $pdo->prepare($sql);

$text = "公務員に冬のボーナス\r\n国家公務員平均約65万円 3年連続の減少";

$prepare->bindValue(':name', 'NHK', PDO::PARAM_STR);
$prepare->bindValue(':body', $text, PDO::PARAM_STR);
$prepare->execute();

echo "insert done";

### csv
L insertする前に、str_replace(‘\r\n’,”\r\n”,$csv);でエスケープする必要がある

$fp = fopen("test.csv", "r");

while(! feof($fp)){
	$csv = fgets($fp);
	$csv = trim($csv,'"');
	$csv = mb_convert_encoding($csv, "UTF-8", "utf-8");
	$csv = str_replace('"','',$csv);
	$csv = str_replace('\r\n',"\r\n",$csv);
	$csv_array = explode(",",$csv);
	

	$stmt = $pdo->prepare("INSERT INTO news (name, body) VALUE (:name, :body)");

	$stmt->bindValue(":name", $csv_array[0], PDO::PARAM_STR);
	$stmt->bindValue(":body", $csv_array[1], PDO::PARAM_STR);
	// $stmt->bindValue(":body", str_replace(["\\r","\\n"], ["\r", "\n"], $csv_array[1]), PDO::PARAM_STR);
	$stmt->execute();
	
}

fgetcsv
L fgetcsvだと、わざわざ\r\nを入れなくても、改行データをそのままmysqlに入れてくれる。

$fp = fopen("hoge.csv", "r");
while(($data[]=fgetcsv($fp))!==FALSE){
}
fclose($fp);

var_dump($data);

foreach($data as $result){
	$stmt = $pdo->prepare("INSERT INTO news (name, body) VALUE (:name, :body)");

	$stmt->bindValue(":name", $result[0], PDO::PARAM_STR);
	$stmt->bindValue(":body", $result[1], PDO::PARAM_STR);
	$stmt->execute();
}
echo "done";

素晴らしい。

[mysql8.0.22] 2000件レコードでテーブルのカラム数28、48、116のselect文の実行時間の差

### レコード数: 2001件
mysql> select count(id) from orders2;
+———–+
| count(id) |
+———–+
| 2001 |
+———–+
1 row in set (0.04 sec)

### sql文
mysql.sql

select * from orders2 where supplier_num = 1;

### カラム数28
$ time (cat mysql.sql | mysql -u root -p test > /dev/null)
Enter password:

real 0m3.099s // プログラムの呼び出しから終了までにかかった実時間
user 0m0.009s // プログラム自体の処理時間(秒)(ユーザCPU時間)
sys 0m0.012s // プログラムを処理するために、OSが処理をした時間

### カラム数48
$ time (cat mysql.sql | mysql -u root -p test > /dev/null)
Enter password:

real 0m3.162s
user 0m0.013s
sys 0m0.009s

### カラム数116
$ time (cat mysql.sql | mysql -u root -p test > /dev/null)
Enter password:

real 0m3.504s
user 0m0.011s
sys 0m0.011s

確かにちょっと遅くなってるけど、カラム数50ぐらいだと、本当に誤差だな
商用環境ではテーブル結合により絞り込み検索を行うので検索スピードは変わってくるのが、レコード数が数千件程度では、カラム数が100を超えていてもそこまで影響はなさそうだな。

[mysql8.0.22]実行速度(ユーザCPU時間)の確認方法

mysql> select version();
+———–+
| version() |
+———–+
| 8.0.22 |
+———–+
1 row in set (0.00 sec)

mysql> use test;
mysql> select * from users;
mysql> SELECT BENCHMARK(100,(SELECT id FROM users m0 WHERE (m0.id = 1)));
+————————————————————+
| BENCHMARK(100,(SELECT id FROM users m0 WHERE (m0.id = 1))) |
+————————————————————+
| 0 |
+————————————————————+
1 row in set (0.00 sec)

ん? 0秒ってこと?

$ touch mysql.sql

select * from users where id = 1;
$ mysql -u root -p "password" -D test <mysql.sql
$ cat mysql.sql | mysql -u root -p -vvv test | tail -n 3
Enter password: 

1 row in set (0.00 sec)

Bye

$ time (cat mysql.sql | mysql -u root -p test > /dev/null)
Enter password: 

real 0m2.544s // プログラムの呼び出しから終了までにかかった実時間(秒)
user 0m0.013s // プログラム自体の処理時間(秒)(ユーザCPU時間)
sys 0m0.007s // プログラムを処理するために、OSが処理をした時間(秒)(システム時間)

0.013s秒ってことか。

これをレコード4000件で、テーブルのカラム数10, 30, 100でテストしてみるか。
結構大変だな。

[PHP 7.4.11] MySQLにCSVインポートする

### dbとテーブルの作成
mysql> create database myapp;
mysql> use myapp;
mysql> create table users(
-> id int primary key auto_increment,
-> name varchar(255),
-> sports varchar(255)
-> );

nameとsportsを入れたcsvファイル(users.csv)を作成します。

### pdoの確認
$ php -i|grep PDO
PDO
PDO support => enabled
PDO drivers => mysql, sqlite
PDO Driver for MySQL => enabled
PDO Driver for SQLite 3.x => enabled

### csv.php

ini_set('display_errors', 1);

try {
		$pdo = new PDO('mysql:host=localhost;dbname=myapp;charaset=utf8','root','****',
				array(PDO::ATTR_EMULATE_PREPARES => false));
	} catch (PDOException $e){
		exit('データベース接続失敗.'.$e->getMessage());
	}

$fp = fopen("users.csv", "r");

while(! feof($fp)){
	$csv = fgets($fp);
	$csv = trim($csv,'"');
	$csv = mb_convert_encoding($csv, "UTF-8", "utf-8");
	$csv = str_replace('"','',$csv);
	$csv_array = explode(",",$csv);

	$stmt = $pdo->prepare("INSERT INTO users(name, sports) VALUES(:name, :sports)");

	$stmt->bindParam(':name', $csv_array[0],PDO::PARAM_STR);
	$stmt->bindParam(':sports', $csv_array[1],PDO::PARAM_STR);
	$stmt->execute();
}

echo "insert done";

$ php csv.php
insert done

mysql> select * from users;
+—-+—————–+—————-+
| id | name | sports |
+—-+—————–+—————-+
| 1 | name | sports
|
| 2 | Efa Trujillo | Gynastics
|
| 3 | Erica Werner | Rowing
|
| 4 | Amin Guerrero | Wiffleball
|
| 5 | Abbie Bender | Rugby
|
| 6 | Kamron Mcneill | Surfing
|
| 7 | Arabella Sutton | Basketball
|
| 8 | Emily Kerr | Curling
|
| 9 | Connar Small | Ice skating
|
| 10 | Haidar Fletcher | Figure skating |
+—-+—————–+—————-+
10 rows in set (0.00 sec)

1行目からinsertされてしまうので、1行目は抜かす必要がありますね。

C言語でMySQLにinsertする書き方

sql_strの箇所をselectからinsert文に変えるだけです。update, deleteも一緒です。

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>

int main(){
	MYSQL *conn = NULL;
	MYSQL_RES *resp = NULL;
     // MYSQL_ROW row;
	char sql_str[255];
	char *sql_serv = "localhost";
	char *user = "root";
	char *passwd = "hogehoge";
	char *db_name = "test";

	memset( &sql_str[0], 0x00, sizeof(sql_str));

	conn = mysql_init(NULL);
	if(!mysql_real_connect(conn, sql_serv, user, passwd, db_name, 0, NULL, 0)){ 
		 exit(-1);
	}

	snprintf(&sql_str[0], sizeof(sql_str)-1, "insert into test (id,name) values (4, 'ddd')"); 
	if(mysql_query(conn, &sql_str[0])){ 
		mysql_close(conn);
		exit(-1);
	}

	mysql_free_result(resp);
	mysql_close(conn);

	return 0;
}

### コンパイル&実行
$ gcc -Wall -o dev hello.c -lmysqlclient -L/usr/lib/x86_64-linux-gnu/
$ ./dev

### 動作確認
mysql> select * from test;
+——+——+
| id | name |
+——+——+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
+——+——+
4 rows in set (0.00 sec)

なるほど、DBとの接続はわかった。次はGUIか。

C言語でMySQLのレコードからSelectする方法

### ソースコード

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>

int main(){
	MYSQL *conn = NULL;
	MYSQL_RES *resp = NULL;
	MYSQL_ROW row;
	char sql_str[255];
	char *sql_serv = "localhost";
	char *user = "root";
	char *passwd = "hogehoge";
	char *db_name = "test";

	memset( &sql_str[0], 0x00, sizeof(sql_str)); // memsetはメモリに指定バイト数分の値をセット, 0x00はヌル文字

	conn = mysql_init(NULL);
	if(!mysql_real_connect(conn, sql_serv, user, passwd, db_name, 0, NULL, 0)){ // サーバ接続開始 mysql_real_connect(mysql, host, user,passwd,db, port,  unix_socket, client_flag)
		 exit(-1);
	}

	// クエリ実行
	snprintf(&sql_str[0], sizeof(sql_str)-1, "select * from test");  // snprintfは指定文字数分だけ文字配列に書き込む
	if(mysql_query(conn, &sql_str[0])){  // sql_str によって指示される SQL ステートメントを実行
		mysql_close(conn);
		exit(-1);
	}

	// レスポンス
	resp = mysql_use_result(conn);  // 結果セットの取得を開始
	while((row = mysql_fetch_row(resp)) != NULL){   // 結果を添字配列として取得する
		printf("%d: %s\n", atoi(row[0]), row[1]);
	}

	mysql_free_result(resp);  // 結果保持用メモリを開放
	mysql_close(conn); // MySQL 接続を閉じる

	return 0;
}

### ライブラリのlibmysqlclient.soの配置場所を確認
$ find / -name “libmysqlclient.so”
/usr/lib/x86_64-linux-gnu/libmysqlclient.so

### コンパイル、実行
// -Wは警告メッセージ抑制, -Lはオブジェクトライブラリ
$ gcc -Wall -o dev hello.c -lmysqlclient -L/usr/lib/x86_64-linux-gnu/
$ ./dev
1: aaa
2: bbb
3: ccc

おいおい、マジかよ。。。
感動した。