Category: MySQL

$ pip3 install mysql-connector-python

mysql側の操作

create database reddit;
use reddit
create table polygon (
	id int auto_increment primary key,
	title varchar(255),
	score int
);

insert into polygon (title, score) values
("Daily Polygon Discussion Thread", 25);

db.py

import mysql.connector

conn = mysql.connector.connect(
user='root',
passwd='hogehoge',
host='localhost',
db='reddit'
)

cur = conn.cursor()

sql = "select * from polygon"
cur.execute(sql)

rows = cur.fetchall()

for row in rows:
	print(row)

cur.close

conn.close

$ python3 db.py
(1, ‘Daily Polygon Discussion Thread’, 25)

### insertの場合

sql1 = "truncate table polygon"
cur.execute(sql1)

sql2 = "INSERT INTO polygon (title, score) VALUES('Daily Polygon Discussion Thread', 4)"
cur.execute(sql2)
conn.commit()

mysql> select * from polygon;
+—-+———————————+——-+
| id | title | score |
+—-+———————————+——-+
| 1 | Daily Polygon Discussion Thread | 4 |
+—-+———————————+——-+
1 row in set (0.00 sec)

OK
これに翻訳を入れたい

from googletrans import Translator
translator = Translator()

text = "Daily Polygon Discussion Thread"
 
translation = translator.translate(text, dest='ja', src='en')
print(translation.text)

$ python3 tran.py
毎日のポリゴンディスカッションスレッド

OK
これを全部つなげる

mysql> select * from polygon;
+—-+———————————+———————————————————–+——-+———————+
| id | title | title_ja | score | date |
+—-+———————————+———————————————————–+——-+———————+
| 1 | Daily Polygon Discussion Thread | 毎日のポリゴンディスカッションスレッド | 4 | 2022-01-08 05:14:42 |
+—-+———————————+———————————————————–+——-+———————+
1 row in set (0.00 sec)

$ 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のデータ移行の方法は理解した。

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 | 〃 仝 ゝ ゞ 々 〆 ヾ ― ‐ ／ 〇 ヽ ＿ ￣ ¨ ｀ ´ ゜ ゛ ＼ § ＾ ≫ ￢ ⇒ ⇔ ∀ ∃ ∠ ⊥ ⌒ ∂ ∇ ≡ ∨ ≪ † √ ∽ ∝ ∵ ∫ ∬ Å ‰ ♯ ♭ ♪ ‡ ～ ′ ≒ × ∥ ∧ ｜ … ± ÷ ≠ ≦ ≧ ∞ ∴ ♂ ♀ ∪ ‥ ° ⊃ ⊂ ⊇ ∩ ⊆ ∋ ∈ 〓 〒 ※ ″ |
+—-+————————————————————————————————————————————————————————————————————————————————————————————————————————-+

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

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

値を慌てて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ではなく、カンマ(,)で区切るのが正解なんだと。。
ビックリするぜ。

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

素晴らしい。

### レコード数: 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を超えていてもそこまで影響はなさそうだな。

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でテストしてみるか。
結構大変だな。