VB.NET-TIPS などプログラミングについて

比較的大きな仮想連番の作成方法について説明します。

仮想的に数千～数万件の連番を返すテーブルを取得する SELECT のSQLを作成してみます。
ネットなどでは以下の様な感じで、例が載っています。
（information_schema.COLUMNS はMySQLが持っているテーブル内のカラムに関する情報用のテーブルです。）

select @no := @no + 1 as no
from (select @no := 0) as dmy, information_schema.COLUMNS
limit 10;

このSQLを実行すると以下の様になります。

MariaDB [test]> select @no := @no + 1 as no
    -> from (select @no := 0) as dmy, information_schema.COLUMNS
    -> limit 10
    -> ;
+------+
| no   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.14 sec)

information_schema.COLUMNS テーブルと「dmy」テーブルの直積のSQLですが、「dmy」は1件しかないので、 information_schema.COLUMNS がもつレコードが全て対象となります。

この方法には少々問題があります。 information_schema.COLUMNS のレコード数には限界があるからで、 実際、私がテストで使っているMySQLでは以下の様に6333件となり、この件数が限界となります。

MariaDB [(none)]> SELECT count(*) FROM information_schema.COLUMNS;
+----------+
| count(*) |
+----------+
|     6333 |
+----------+
1 row in set (7.27 sec)

もう少し、仮想連番テーブルの最大番号を大きくし、制御できる様に考えてみます。
information_schema.COLUMNS は必ずどのデータベースにも存在するので、これを利用するのですが、 information_schema.COLUMNS から10件ずつ取得したものから連番を発生させるサブクエリを2個用意して、 それらの直積を取る形で、仮想連番テーブル用の元テーブルとします。

文章で説明してもピンときませんので、以下のSQLを見て下さい。

SELECT * FROM (
    (select @no1 := @no1 + 1 as no1 from (select @no1 := 0) as dmy, information_schema.COLUMNS limit 10) X
   ,(select @no2 := @no2 + 1 as no2 from (select @no2 := 0) as dmy, information_schema.COLUMNS limit 10) X1
);

これを実行すると以下の様に「10×10」のデータが表示されます。

MariaDB [(none)]> SELECT * FROM (
    ->     (select @no1 := @no1 + 1 as no1 from (select @no1 := 0) as dmy, information_schema.COLUMNS limit 10) X
    ->    ,(select @no2 := @no2 + 1 as no2 from (select @no2 := 0) as dmy, information_schema.COLUMNS limit 10) X1
    -> );
+------+------+
| no1  | no2  |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    4 |    1 |
|    5 |    1 |
|    6 |    1 |
|    7 |    1 |
|    8 |    1 |
|    9 |    1 |
|   10 |    1 |
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |

... (省略) ...

|    7 |   10 |
|    8 |   10 |
|    9 |   10 |
|   10 |   10 |
+------+------+
100 rows in set (0.30 sec)

このクエリを元にして、仮想連番テーブルを生成するSQLを作ると以下の様になります。

SELECT @no := @no + 1 AS idx
FROM (select @no := 0) as dmy,
(
    SELECT * FROM (
            (select @no1 := @no1 + 1 as no1 from (select @no1 := 0) as dmy, information_schema.COLUMNS limit 10) X
           ,(select @no2 := @no2 + 1 as no2 from (select @no2 := 0) as dmy, information_schema.COLUMNS limit 10) X1
    ) 
) XX
LIMIT 10;

これを実行すると以下の様に1～10の連番を返すことができます。

MariaDB [(none)]> SELECT @no := @no + 1 AS idx
    -> FROM (select @no := 0) as dmy,
    -> (
    ->     SELECT * FROM (
    ->             (select @no1 := @no1 + 1 as no1 from (select @no1 := 0) as dmy, information_schema.COLUMNS limit 10) X
    ->            ,(select @no2 := @no2 + 1 as no2 from (select @no2 := 0) as dmy, information_schema.COLUMNS limit 10) X1
    ->     )
    -> ) XX
    -> LIMIT 10;
+------+
| idx  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.25 sec)

サブクエリの LIMIT で生成されるデータ数の最大を決定していますが、この値を「100」にすれば最大は「10000」になります。 また、サブクエリの SELECT 文を更に追加して以下の様にすれば「10×10×10×10」が最大となります。
さらに「10」のところを「100」にすれば1億までの連番を返すことができます。（そこまでの必要性はありませんが）

SELECT @no := @no + 1 AS idx
FROM (select @no := 0) as dmy,
(
    SELECT * FROM (
            (select @no1 := @no1 + 1 as no1 from (select @no1 := 0) as dmy, information_schema.COLUMNS limit 10) X
           ,(select @no2 := @no2 + 1 as no2 from (select @no2 := 0) as dmy, information_schema.COLUMNS limit 10) X1
           ,(select @no3 := @no3 + 1 as no3 from (select @no3 := 0) as dmy, information_schema.COLUMNS limit 10) X2
           ,(select @no4 := @no4 + 1 as no4 from (select @no4 := 0) as dmy, information_schema.COLUMNS limit 10) X3
    ) 
) XX
LIMIT 10;

SELECT の出力に連番を振りたい場合は良くあると思います。 主キーは削除したりすれば欠番が生じたりしますし、いろんな条件を付加したりソート指定を行って連番を表示したい場合があります。

そんな時には、MySQL の ユーザー定義変数 を SET コマンドで宣言し、それをカウンタの様にして使うことで 連番の発生を行うことができます。

SET @no = 0;
SELECT (@no := @no + 1) as no, t_auto.* from t_auto order by id desc;

このSQLを実行すると以下の様になります。

MariaDB [test]> SET @no = 0;
Query OK, 0 rows affected (0.39 sec)

MariaDB [test]> SELECT (@no := @no + 1) as no, t_auto.* from t_auto order by id desc;
+------+----+--------+
| no   | id | val    |
+------+----+--------+
|    1 |  5 | test-a |
|    2 |  4 | test4  |
|    3 |  2 | test2  |
|    4 |  1 | test1  |
+------+----+--------+
4 rows in set (0.22 sec)

SET コマンドの部分を SELECT の FROM句 の中に入れてしまうと以下の様にできます。

select @no := @no + 1 as no, t_auto.*
from (select @no := 0) as dmy, t_auto
order by id desc;

このSQLを実行すると以下の様になります。

MariaDB [test]> select @no := @no + 1 as no, t_auto.*
    -> from (select @no := 0) as dmy, t_auto
    -> order by id desc;
+------+----+--------+
| no   | id | val    |
+------+----+--------+
|    1 |  5 | test-a |
|    2 |  4 | test4  |
|    3 |  2 | test2  |
|    4 |  1 | test1  |
+------+----+--------+
4 rows in set (0.00 sec)

MySQL では ユーザー定義変数 を SET コマンドにて宣言できます。 定義できる値はリテラル値とスカラーサブクエリーによって返される値となります。
ユーザー定義変数は@var_name として記憶され、次のように式の値（expr）が割り当てられます

SET @var_name = expr;

リテラル値とスカラーサブクエリー値の宣言は以下の様になります。
なお、スカラーサブクエリー値の宣言の「@var4」の右辺の式の部分は全体を「()」括弧で囲む必要があります。

SET @var1 = 100;
SET @var2 = 'aaaaaaaa';
SET @var3 = STR_TO_DATE('2024-01-01', '%Y-%m-%d');
SET @var4 = (select count(*) from t_auto);

それぞれの変数の値を取得してみると以下の様になります。

MariaDB [test]> SELECT @var1, @var2, date_format(@var3, '%Y/%m/%d'), @var4;
+-------+----------+--------------------------------+-------+
| @var1 | @var2    | date_format(@var3, '%Y/%m/%d') | @var4 |
+-------+----------+--------------------------------+-------+
|   100 | aaaaaaaa | 2024/01/01                     |     3 |
+-------+----------+--------------------------------+-------+
1 row in set (0.05 sec)

それではこのスカラーサブクエリー値「@var4」はクエリの結果を返しているので「t_auto」テーブルに新規データを追加するとどうなるかを調べてみます。
データの追加は以下のSQLで実行します。

INSERT INTO t_auto(val) VALUES('test-a');

「@var4」の値を取得してみます。

MariaDB [test]> INSERT INTO t_auto(val) VALUES('test-a');
Query OK, 1 row affected (0.19 sec)

MariaDB [test]> SELECT @var4;
+-------+
| @var4 |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

上記の結果から、「@var4」は宣言された時の値を保持していることが分かります。 「@var4」を更新したいのであれば、再度 SET を行う必要があります。

MariaDB [test]> SET @var4 = (select count(*) from t_auto);
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT @var4;
+-------+
| @var4 |
+-------+
|     4 |
+-------+
1 row in set (0.00 sec)

各種マスタやトランザクションテーブルの構造として、ユニークな主キーとなる AUTO_INCREMENT カラムを持ち、必要であれば、その他のカラムでインデックスを持ったりします。

AUTO_INCREMENT カラムは INSERT 時に指定しなくても MySQL がユニークな数値を割り振ってくれます。
そのため、データを登録する場合は、主キーの値を考えずにどんどん INSERT ができます。

ただし、紐付けするデータが無い場合は良いのですが、伝票データ等でヘッダデータと明細データのテーブルが在る場合、 最初にヘッダデータを登録し、その後でヘッダデータの主キーに紐づく様に明細データを登録することになります。

ヘッダデータの INSERT の後、主キーの値を取得する必要が出てきますが、 そこで LAST_INSERT_ID() 関数の出番となります。
この LAST_INSERT_ID() 関数ですが AUTO_INCREMENT カラムを持つテーブルに対して INSERT 後に SELECT を行えば、主キーの値が取得できます。

テスト用のテーブル生成SQL

先ずはテスト用のテーブルの生成を行います。
以下の通り主キーとして「id」カラム、さらにデータとしてVARCHAR型の「val」カラムを宣言します。

CREATE TABLE t_auto (
    id  INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    val VARCHAR(32) NOT NULL
);

それではこのテーブルにデータを INSERT して LAST_INSERT_ID() を取得してみます。

レコードの追加とAUTO_INCREMENT値の取得を行うSQL

INSERT INTO t_auto(val) VALUES('test1');
SELECT LAST_INSERT_ID();
SELECT * FROM t_auto;

実行結果は以下の通りです。

MariaDB [test]> INSERT INTO t_auto(val) VALUES('test1');
Query OK, 1 row affected (0.06 sec)

MariaDB [test]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t_auto;
+----+-------+
| id | val   |
+----+-------+
|  1 | test1 |
+----+-------+
1 row in set (0.00 sec)

MariaDB [test]>

上記の結果から、自動で主キーが割り振られたことが分かります。
再度、「val」カラムに 'test2' のデータを設定してみますと、以下の様になります。

MariaDB [test]> INSERT INTO t_auto(val) VALUES('test2');
Query OK, 1 row affected (0.05 sec)

MariaDB [test]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t_auto;
+----+-------+
| id | val   |
+----+-------+
|  1 | test1 |
|  2 | test2 |
+----+-------+
2 rows in set (0.00 sec)

MariaDB [test]>

確かに、AUTO_INCREMENT のカラムが加算されているのが分かります。

さて、ここでトランザクション処理を INSERT の前後にはさむ形にして、追加処理をロールバックしてみます。
以下の様なSQLを実行します。

トランザクション処理でINSERTをはさむSQL

BEGIN;
INSERT INTO t_auto(val) VALUES('test3');
ROLLBACK;
SELECT LAST_INSERT_ID();
SELECT * FROM t_auto;

実行結果は以下の様になります。
INSERT は確かにロールバックされてテーブルに 'test3' のデータは残っていませんが、 主キーの「id」は1個加算され「3」になりました。

この結果が大事なところで、トランザクション処理では現在の AUTO_INCREMENT のカラムの値が、他の接続先からの INSERT で値が競合しない様になっていますので、今回の様に AUTO_INCREMENT 値は「3」となります。

よって次に INSERT 処理を正常に行えば AUTO_INCREMENT 値は「4」となります。

MariaDB [test]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO t_auto(val) VALUES('test3');
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t_auto;
+----+-------+
| id | val   |
+----+-------+
|  1 | test1 |
|  2 | test2 |
+----+-------+
2 rows in set (0.00 sec)

MariaDB [test]>

今度はトランザクションをコミットする様にして実行すると、確かに AUTO_INCREMENT 値は「4」となっています。

MariaDB [test]> BEGIN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO t_auto(val) VALUES('test4');
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> COMMIT;
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t_auto;
+----+-------+
| id | val   |
+----+-------+
|  1 | test1 |
|  2 | test2 |
|  4 | test4 |
+----+-------+
3 rows in set (0.00 sec)

MariaDB [test]>

トランザクション的なテーブルを、同一構造のワークテーブルにバックアップコピーを取っておいて、 ワークテーブルから元のテーブルに差分を復活させたい場合がよくあると思います。

とあるシステムのデバッグを行う時に、元のテーブルに対して削除処理が入る場合、 デバッグの前にワークテーブルにバックアップコピーを取っておきます。
その後、デバッグを行っていると削除でデータが無くなるので、データの復活が出来れば、再度最初からデバッグ処理が行えます。
そこで、最初にバックアップを行うSQLから示します。
尚、今回のテストではテーブルの内容については深く考えていませんので、テーブル名は TT_TEST としワークテーブルは TW_TEST とし、 データをにはユニークキーとして KEY の名前のカラムが存在するとします。

バックアップを行うSQL

' ワークテーブルの作成
CREATE TABLE TW_TEST LIKE TT_TEST;

' ワークテーブルへのコピー
TRUNCATE TABLE TW_TEST;
INSERT INTO TW_TEST SELECT * FROM TT_TEST;

取敢えず、ワークテーブルの作成についても記しています。
「CREATE TABLE ... LIKE ステートメント」はすでに存在するテーブルのテーブル定義のみをコピーして、新規にテーブルを作成することができます。
なお、オラクルのSQLでも同様のことができるようです。

実テーブルに存在しないワークテーブルのレコードの追加を行うSQL

MySQLにはオラクルの様に集合演算子の MINUS が無いため、ユニークキーの結合で存在しないものを選択し、そのデータをINSERTすることになります。
SQLは以下の様になります。

INSERT INTO TT_TEST
SELECT *
FROM TW_TEST AS T1
WHERE NOT EXISTS (
	SELECT *
	FROM TT_TEST AS T2
	WHERE T1.KEY = T2.KEY
);