MySQLの覚書 | ワクガンス

INSERTしたレコードのidを取得（AUTO_INCREMENT 型のidである場合） | LAST_INSERT_ID()

INSERTの実行直後に下記を実行すること。
また、トランザクションの中で実行すること。

SELECT LAST_INSERT_ID()

2019-1-11

緯度経度フィールドを含むサンプルデータを100万件作成

フィールド	型
id	int(主キー auto)
big_name	varchar(64)
lat	double
lng	double

100万件分の行を作成する。(二の二十乗=104万...)


	INSERT INTO bigs () VALUES ();
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;
	INSERT INTO bigs (id) SELECT 0 FROM bigs;

一括サンプルデータ入力


	UPDATE bigs SET
	  big_name = CONCAT('ビッグマン', id),
	  lat =RAND() * 180 - 90,
	  lng =RAND() * 360 - 180

参考サイト

2019-8-19

緯度経度による検索を早くするためのチューニング

以下のチューニングを行うと緯度経度による検索(SELECT)は速くなるが挿入、更新、削除は遅くなる。

フィールド	型
id	int(主キー)
big_name	varchar(64)
lat	double(8,6)
lng	double(9,6)

まず緯度経度の桁数を制限。
lat(緯度)の型は「double(8,6)」すなわち桁数8,小数点以下の桁数6にする。
lng(経度)の型は「double(9,6)」、すなわち桁数9,小数点以下の桁数6にする。

latとlngにインデックスを追加。


		ALTER TABLE bigs ADD INDEX lat_index(lat);
		ALTER TABLE bigs ADD INDEX lng_index(lng);

以上でチューニング完了。

検証結果について

100万件データで下記の検索SQLを実行したときの計測時間を測定。

SELECT big_name, lat, lng from bigs where lat > 26 AND lat < 27 AND lng > 127 AND lng < 128

計測時間
チューニング前→ クエリの実行時間： 1.2658 秒
チューニング後→ クエリの実行時間： 0.5152 秒

倍以上の改善が見られた。

2019-8-19

Alterで列の変更や追加ができない | #1067-'post_date' | sql_mode

datetime型のフィールドでデフォルト値に「0000-00-00 00:00:00」が入っているとAlterによる列編集でエラーが起きるようになる。

対策

my.iniを開き、sql_modeを書き換える。

	#sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
	sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2019-10-21

文字を置換する | REPLACE

nekoを猫に置換する例。

SELECT REPLACE(main_image, "neko", "猫") FROM animals

2019-10-24

MySQLのスキーマ

MySQLの場合、スキーマとデータベースは同じ。Oracleではスキーマとデータベースは異なる。

2019-1-11

rootのパスワードを変更→ phpMyAdminで接続できません mysqli::real_connect(): (HY000/1045) ...

rootのパスワードを変更すると、phpMyAdminで以下のエラーが発生して接続できなくなる。

	エラー
	MySQL のメッセージ: ドキュメント
	
	接続できません。設定が無効です。
	 mysqli::real_connect(): (HY000/1045): Access denied for user 'root'@'localhost' (using password: NO)
	 MySQL サーバに接続しようとしましたが拒否されました。config.inc.php のホスト、ユーザ名、パスワードが MySQL サーバの管理者から与えられた情報と一致するか確認してください。

修正手順

config.inc.phpをテキストエディタで開く
xamppの場合の例→C:\xampp\phpMyAdmin\config.inc.php
passwordの箇所にrootに設定しているパスワードを入力
XAMPP Control PanelなどにてMySQLを再起動
以上で修正完了

2020-6-28

DockerコンテナとMySQLのバックアップおよびリストア(インポート)

バックアップ

docker exec -it コンテナ名 bash -c 'MYSQL_PWD=パスワード mysqldump -u ユーザー名 DB名' 1> バックアップファイル名

$ docker exec -it docker_demo_mysql_1 bash -c 'MYSQL_PWD=root mysqldump -u root animal' 1> sql.dump

リストア

$ docker cp animal.sql docker_demo_mysql_1:/tmp/animal.sql
$ docker exec -it docker_demo_mysql_1 bash
# mysql -u root -p animal < /tmp/animal.sql

2021-1-27

テストデータ作成に役立つSQL | サンプルデータ

レコードを2倍に増やすSQL

INSERT INTO animals (name) SELECT name FROM animals

全レコードにレコードごとに異なる数値をランダムでセットする。下記の例は1から4までの自然数をvalue1フィールドにランダムでセットします。

UPDATE animals SET value1 = FLOOR(1 + RAND() * 4)

idを利用して適当な文字列をセットする

UPDATE animals SET name = CONCAT('ネコ' , id)

ランダムな日付をセットする。下記の例は2022-10-01から2022-10-31の範囲でランダムな日付をセットしている。

UPDATE animals SET animal_date = ADDDATE('2022-10-01', 31*rand());

選択肢または決められた指定文字列をランダムでセットする

UPDATE animals SET animal_type = CASE FLOOR(RAND()*4) WHEN 0 THEN "哺乳類" WHEN 1 THEN "両生類" WHEN 2 THEN "魚類" WHEN 3 THEN "爬虫類" END

ランダムなコードを作成する。下記の例では8桁の半角英数字をセットする例。

UPDATE items SET item_name  = CONCAT('お酒-' , SUBSTRING(MD5(RAND()),1,8))

2022-10-23 | 2022-11-12

xxx

2019-1-11