MySQLの覚書

データを倍にする

サンプルテーブル(log_xs)

フィールド	型	Null	主キー	デフォルト	コメント
id	int(11)	NO	PRI		ID
user_id	int(11)	NO			ユーザーID
event_no	int(11)	NO			イベント番号
event_cnt	int(11)	NO			イベント実行回数
created_ip_addr	varchar(40)	YES			生成IPアドレス
modfied_ip_addr	varchar(40)	NO			更新IPアドレス
created	datetime	NO			生成日時
modified	timestamp	YES		CURRENT_TIMESTAMP	更新日時

データを倍にするSQL

	INSERT INTO log_xs(
		user_id
		,event_no
		,event_cnt
		,created_ip_addr
		,modfied_ip_addr
		,created)  
		(
			SELECT 
				user_id
				,event_no
				,event_cnt
				,created_ip_addr
				,modfied_ip_addr
				,created
			 FROM log_xs
		);

1件レコードから上記のSQLを28回実行すると1億件レコードができあがる。（134217728件）
MySQLが初期設定のままだと、途中で容量オーバーになるので、テーブルのサイズを大きくする設定を行う。

テーブルのサイズを大きくする

巨大データを作成中に以下のエラーが表示された場合、テーブルのサイズを大きくする。

The total number of locks exceeds the lock table size

MySQLのmy.iniを開き、以下のコードを書き換えて保存する。

innodb_buffer_pool_size = 16M

↓16Mから256Mに書き換え

innodb_buffer_pool_size = 256M

XAMPP Control PanelでMySQLを再起動する。

参照

パーティション

パーティションによりテーブルを分割できる。
テーブルのデータが巨大になりそうなときに利用すると良い。

利点

探索SQLの実行速度が速くなる。無駄な処理を省いたり、キャッシュの有効活用により速くなるらしい。
一括削除が速くなる。巨大データの削除には時間がかかるが、分割した分をDROPでまるごと削除することにより高速になる。

欠点

分割する列を主キーにする必要がある。
主キーにしていない場合、以下のエラーが表示される。

#1503 - A PRIMARY KEY must include all columns in the table's partitioning function

参考

データ分割方法

パーティションのデータ分割方法には、「RANGE、LIST、HASH、KEY」がある。
RANGEによる、日付を基準に分割する場面が多い。

パーティションの例

サンプルテーブル log_ys

フィールド	型	主キー	コメント
id	int(11)	PRI	ID(自動採番:auto increment)
user_id	int(11)		ユーザーID
event_no	int(11)		イベント番号
created←分割基準	datetime	PRI←分割基準にするため主キーとする	生成日時

log_ysのサンプルデータ

id	user_id	event_no	created
2204636	2	7	2015/9/2 00:00:00
2204637	2	9	2015/10/2 00:00:00
2204638	2	10	2015/8/2 00:00:00
2204639	2	11	2015/7/2 00:00:00
2204643	2	12	2015/6/2 00:00:00
2204644	2	13	2015/5/2 00:00:00
2204645	2	14	2015/4/2 00:00:00
2204646	2	15	2015/3/2 00:00:00
2204647	2	16	2015/2/2 00:00:00
2204648	2	17	2015/1/2 00:00:00
2204649	2	18	2014/12/2 00:00:00
2204650	2	19	2013/12/2 00:00:00
2204651	8	8	0000-00-00 00:00:00
2204634	2	6	2015/11/2 00:00:00
1	2	3	2015/12/2 00:00:00
2204632	2	4	2015/12/3 00:00:00
2204633	2	5	2015/12/31 00:00:00
2204652	7	7	2015/12/31 00:00:00

パーティション分割をするSQL

	ALTER TABLE log_ys
	PARTITION BY RANGE COLUMNS(created) 
	(
		PARTITION p20150901 VALUES LESS THAN ('2015-09-01') ENGINE = InnoDB,
		PARTITION p20151001 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
		PARTITION p20151101 VALUES LESS THAN ('2015-11-01') ENGINE = InnoDB,
		PARTITION p20151201 VALUES LESS THAN ('2015-12-01') ENGINE = InnoDB,
		PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE=InnoDB
	);

※日付は昇順にすること。

パーティションを確認するSQL

	SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS
	FROM INFORMATION_SCHEMA.PARTITIONS
	WHERE TABLE_NAME='log_ys';

出力

TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	PARTITION_ORDINAL_POSITION	TABLE_ROWS
cake_demo	log_ys	p20150901	1	11
cake_demo	log_ys	p20151001	2	0
cake_demo	log_ys	p20151101	3	0
cake_demo	log_ys	p20151201	4	0
cake_demo	log_ys	pmax	5	3

パーティションを解除するSQL

	ALTER TABLE log_ys REMOVE PARTITIONING;

パーティションを削除

※データごと削除

	ALTER TABLE log_ys DROP PARTITION p20151101;

パーティションを挿入

	ALTER TABLE log_ys REORGANIZE PARTITION pmax INTO (
	PARTITION p20160101 VALUES LESS THAN ('2016-01-01'),
	PARTITION pmax VALUES LESS THAN MAXVALUE);

パーティション挿入でエラーになる例

最後のパーティションが「2016-01-01」であるなら、この日付以降の日付で分割しなければならない。
以前日「2014-02-01」でパーティションを作ろうとするとエラーになる。
以降日「2016-02-01」でパーティションを作成可能。

	ALTER TABLE log_ys REORGANIZE PARTITION pmax INTO (
	PARTITION p20140101 VALUES LESS THAN ('2014-02-01'),
	PARTITION pmax VALUES LESS THAN MAXVALUE);

エラー

#1493 - VALUES LESS THAN value must be strictly increasing for each partition

参考サイト

重複レコードを無視 | INSERT IGNORE

IGNOREを指定するとINSERTにする際、重複する値があれば、新規レコード追加をキャンセルする。（SQLエラーにならず）
ただしユニーク（PRIMARYかUNIQUE）のフィールドのみ重複チェックする。
ユニークでなければ、普通に追加されてしまう。

IGNORE指定の例

INSERT IGNORE INTO nekos (id,text1) VALUES(2,'kani');

検証

nekosテーブル idはPRIMARY

id	val1	text1	test_date	test_dt
1	1	neko	2014/4/1	2014/12/12 0:00
2	2	kani	2014/4/2	2014/12/12 0:00
4	4	buta	2014/4/4	2014/12/12 0:00
5	3	yagi	2014/4/3	2014/12/12 0:00

INSERT IGNORE INTO nekos (id,text1) VALUES(2,'kani');

idがユニーク（PRIMARY）であり、id=2はテーブル上に存在するため、新規レコードは追加されない。なおエラーにはならない。

INSERT IGNORE INTO nekos (id,text1) VALUES(12,'kani');

idがユニークであり、id=12はテーブル上に存在しないので、新規レコードは追加される。

INSERT IGNORE INTO nekos (val1,text1) VALUES(2,'kani');

val1,text1は重複値であるが、どちらもユニークでないため、新規レコードは追加される。

テーブルの容量サイズを取得（ついでに行数や平均行容量も）

テーブルデータ量を取得するSQL

	SELECT
		table_name,
		engine,
		table_rows, # テーブルの行数
		avg_row_length, # 平均行容量（１レコードあたりの平均容量）
		(data_length + index_length) AS all_length, # テーブルの容量サイズ(byte)
		data_length,
		index_length
	FROM
		information_schema.tables 
	WHERE
		table_schema = 'animal_park' # DB名

テーブルデータ量の例

engine	table_name	table_rows	avg_row_length	all_length	data_length	index_length
InnoDB	kanis	10	1638	16384	16384	0
InnoDB	log_xs	120997206	82	10022289408	10022289408	0
InnoDB	log_ys	20	5734	229376	114688	114688
InnoDB	nekos	9	1820	16384	16384	0

説明表

フィールド	説明
table_name	テーブル名
engine	innoDB,MyISAMなど
table_rows	レコード件数であるが、innoDBである場合、正確値でなく見積もり値となる。
avg_row_length	平均レコードサイズ(byte)
all_length	合計データ量(データ量＋インデックスサイズ)
data_length	データ量(byte)
index_length	インデックスサイズ(byte)。テーブル内のインデックスが占めるサイズ。

参考:Rexent Blog 2015-12-28 | 2018-5-29

DBのサイズ（容量）を取得する

	SELECT 
		table_schema, sum(data_length) AS db_length # DB容量
	FROM 
		information_schema.tables
	WHERE
		table_schema = 'animal_park' # DB名

table_schema	db_length
animal_park	65536

2018-5-29

レコードのコピー

レコードをコピーするSQLの例

INSERT INTO nekos (val1,text1,test_date,test_dt) (select val1,text1,test_date,test_dt FROM nekos WHERE id = 5);

検証

id=5のレコードをコピーして新しいレコードを作成する。
nekosテーブルは、idは主キーかつ自動である。

SQL実行前のnekosテーブル

id	val1	text1	test_date	test_dt
4	4	buta	2014/4/4	2014/12/12 0:00
5	3	yagi	2014/4/3	2014/12/12 0:00
6	3	ari	2014/4/3	2014/12/12 0:00

SQLを実行する

INSERT INTO nekos (val1,text1,test_date,test_dt) (select val1,text1,test_date,test_dt FROM nekos WHERE id = 5);

SQL実行後のnekosテーブル

id	val1	text1	test_date	test_dt
4	4	buta	2014/4/4	2014/12/12 0:00
5	3	yagi	2014/4/3	2014/12/12 0:00
6	3	ari	2014/4/3	2014/12/12 0:00
15	3	yagi	2014/4/3	2014/12/12 0:00

誤

以下のSQLでは主キーのエラーが起こる

INSERT INTO nekos  (select * FROM nekos WHERE id = 5);

id以外のレコードをコピーする

	CREATE TEMPORARY TABLE tmp SELECT * from animals where id=1234;
	ALTER TABLE tmp drop id;
	INSERT INTO animals SELECT 0,tmp.* FROM tmp;
	DROP TABLE tmp;

プログラムに埋め込んでの使用はふさわしくない。
とりあえずレコードのコピーが欲しいときに。

2019-11-30

SQLに変数名を組み込む | 動的SQL

SQLに変数名を指定することができる。

	SET @a1='neko';
	SELECT * FROM nekos WHERE text1 = @a1;

このSQLの用い方は、動的SQL、ダイナミックSQL、ストアドプロシージャとも呼ばれる。

phpMyAdminで大容量ファイルをインポートできるようにする

phpMyAdminのインポートで大容量SQLファイルを読み込もうとするとエラーになる。
この場合、アップロード容量を増やすPHP.iniの設定をすれば良い。

phpMyAdminからインポートしたsqlファイルには、INSERT文以外にも様々な短いSQL文が存在する。
データ投入するだけならINSERT文以外のSQLは削除しても良い。
なおsqlファイルを編集すると、一部の文字が文字化けすることがあるので注意。（「～」全角チルダと波ダッシュの文字化けを参考）

最大値のレコードを取得（すべてのフィールドを取得）

ORDERとLIMITを使えば、簡単に最大値を持つレコードを取得できる。

SELECT * FROM nekos ORDER BY neko_date DESC LIMIT 1

旧式

nekosテーブル

id	neko_val	neko_name	neko_date	neko_group	neko_dt	note
1	1	ルドルフ	2014/4/1	2	2014/12/12 0:00	字を学ぶ
2	25	吾輩は猫	2016/6/24	1	2014/12/12 0:00	名前はまだない
4	4	100万回生きた猫	2014/4/4	4	2014/12/12 0:00	100万回死んだ。
9	111	イッパイアッテナ	2014/4/1	3	2014/4/28 10:04	識字者
13	99	ピーター	2016/6/25	6	2016/1/25 12:12	畑を荒らすウサギ
14	200	ロイヤルアナロスタン	2016/2/8	5	NULL	荒野を生き抜いた。

最大値のレコードを取得するSQL

最大日付のレコードを、すべてのフィールドと共に取得する。


	SELECT *
	FROM nekos
	WHERE (neko_date) IN
	(SELECT MAX(neko_date) FROM nekos );

出力

id	neko_val	neko_name	neko_date	neko_group	neko_dt	note
13	99	ピーター	2016/6/25	6	2016/1/25 12:12	畑を荒らすウサギ

注意：最大値や最小値が複数存在する場合

最小日付のレコードが２つあれば、取得されるレコードも２つである。

	SELECT *
	FROM nekos
	WHERE (neko_date) IN
	(SELECT MIN(neko_date) FROM nekos );

出力

id	neko_val	neko_name	neko_date	neko_group	neko_dt	note
1	1	ルドルフ	2014/4/1	2	2014/12/12 0:00	字を学ぶ
9	111	イッパイアッテナ	2014/4/1	3	2014/4/28 10:04	識字者

2016-6-29 | 2022-1-14

idが存在しないテーブルにidと連番を入力する

	ALTER TABLE many_todos ADD id int not null primary key auto_increment;
	ALTER TABLE many_todos MODIFY id int FIRST;

1番目のSQLで、id列追加と連番入力する。
2番目のSQLで、列の先頭にid列を移動する。

2016-7-6

TEST

テスト

xx