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

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

以前、以下の記事などで紹介した内容ですが、PHPのバージョンを「7.4」にアップした時についでにPhpSpreadsheetを「1.25」のバージョンにしたのですが、 下のプログラムを実行するとエクセルファイルは作成されますが、チャートはエラーが出て表示されません。
⇒PHP PhpSpreadsheet ワークシート上にチャート(グラフ)を作成する方法について（PhpSpreadsheet\Chart）

該当エクセルファイルを開くとエラーが表示される。

■チャートが表示されないソース

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\IOFactory;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// ワークシートオブジェクト取得
$objWorksheet = $objSpreadsheet->getActiveSheet();
//  チャート用テストデータ生成
$objWorksheet->fromArray(
	array(
		array('売上' , 2016, 2017, 2018, 2019, 2020),
		array('商品1',   12,   15,   21,   18,   20),
		array('商品2',   18,   19,   23,   14,   21),   //追加
		array('商品3',   15,   10,   20,   12,   23),   //追加
	)
);

//  X軸ラベルの指定
$arrCategorysDataSeries = array(
	new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$1:$F$1', NULL, 5),	//	2016 to 2020
);
//  描画データの指定
$arrDataSeriesValues = array(
	new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$2:$F$2', NULL, 5),  //商品1
	new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$3:$F$3', NULL, 5),  //商品2　追加
	new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$4:$F$4', NULL, 5),  //商品3　追加
);
//	チャート・データシリーズの生成
$objSeries = new DataSeries(
	DataSeries::TYPE_LINECHART,                     // plotType
    NULL,                                           // plotGrouping(DataSeries::GROUPING_STANDARD)
	range(0, count($arrDataSeriesValues) - 1),      // plotOrder
	array(),                                        // plotLabel
	$arrCategorysDataSeries,                        // plotCategory
	$arrDataSeriesValues                            // plotValues
);

//	プロットエリアにチャート・データシリーズに設定
$objPlotArea = new PlotArea(NULL, array($objSeries));

//  チャート・タイトル生成
$objTitle = new Title('売上データ');
//	チャート生成
$objChart = new Chart(
	'chart1',       // name
	$objTitle,      // title
	NULL,           // legend
	$objPlotArea,   // plotArea
	TRUE,           // plotVisibleOnly
	0,              // displayBlanksAs
	NULL,           // xAxisLabel
	NULL		    // yAxisLabel
);
//	ワークシート内のチャート位置設定
$objChart->setTopLeftPosition('A6');        // 左上
$objChart->setBottomRightPosition('G18');   // 右下

//	ワークシートにチャート追加
$objWorksheet->addChart($objChart);

// [test-g-1-2.xlsx]:Excel2007形式で保存する
$objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
$objWriter->setIncludeCharts(TRUE);
$objWriter->save('test-g-1-2.xlsx');
exit();
?>

このソースでチャートを表示させるには「チャート生成:new Chart」の引数の中の「displayBlanksAs」を「0」ではなく以下のどれかを指定する必要があります。

■PhpOffice\PhpSpreadsheet\Chart\DataSeries.php 内の宣言

    const EMPTY_AS_GAP = 'gap';
    const EMPTY_AS_ZERO = 'zero';
    const EMPTY_AS_SPAN = 'span';

よって「チャート生成:new Chart」部分のソースは以下の様にします。

//	チャート生成
$objChart = new Chart(
	'chart1',       // name
	$objTitle,      // title
	NULL,           // legend
	$objPlotArea,   // plotArea
	TRUE,           // plotVisibleOnly
	DataSeries::EMPTY_AS_GAP,              // displayBlanksAs
	NULL,           // xAxisLabel
	NULL		    // yAxisLabel
);

PhpSpreadsheetの記事の中え「チャート生成:new Chart」の引数の中の「displayBlanksAs」を「0」にしているものがほとんどですので、 上記の様に読み替えて下さい。

関連する記事

画像ファイルをHTMLに記述して拡大表示を行う場合に良く使う「LightBox2」で、 Javascriptのプログラムから画像追加のテストを行ってみました。

「LightBox2」と1個の画像のみの表示の場合は、以下の様な感じでHTMLを作れば画像の拡大表示ができます。

■LightBox2の例

<!DOCTYPE html>
<head>
<link href="./js/lightbox2-2.11.4/css/lightbox.min.css" rel="stylesheet">
<script type="text/javascript" src="./js/jquery-3.6.0/jquery-3.6.0.min.js"></script>
<script src="./js/lightbox2-2.11.4/js/lightbox-plus-jquery.min.js"></script>
<script>
//  lightbox start
$(function() {
	lightbox.option({
		"positionFromTop": 150,
	});
});
</script>

</head>
<body>
<h1>LightBox2-TEST</h1>
<a href="./test-1.jpg" data-lightbox="album" >
  <img src="./test-1.jpg" id="" width="100" >
</a>
</body>
</html>

上のHTMLでは特に難しくはありませんでした。 そこで、このHTMLにボタンと、10個の適当な画像ファイル（add-1.jpg ～add-10.jpg）を用意して、 そのボタンをクリックすることで、上記の「test-1.jpg」のファイルの後ろに表示する様にしてみます。

画像ファイルの指定を追加しやすくするために、<div id="imgdiv"></div> で囲っています。
この35行は、divタグ、aタグ、imgタグを1行で記述していますが、こうしないと余計なキャプションが付くための対策です。

■LightBox2の例・その２

<!DOCTYPE html>
<head>
<link href="./js/lightbox2-2.11.4/css/lightbox.min.css" rel="stylesheet">
<script type="text/javascript" src="./js/jquery-3.6.0/jquery-3.6.0.min.js"></script>
<script src="./js/lightbox2-2.11.4/js/lightbox-plus-jquery.min.js"></script>

<script>
//  lightbox start
$(function() {
	lightbox.option({
		"positionFromTop": 150,
	});
});

$(function() {
	$("#btn").on('click', function() {
		for (i = 1; i <= 10; i++) {
			var add = "";
			add += '<a href="./add-'+i+'.jpg" data-lightbox="album" >';
			add += '<img src="./add-'+i+'.jpg" width="100" >';
			add += '</a>';
			$("#imgdiv").append(add);
		}

	});

});
</script>

</head>
<body>
<h1>LightBox2-TEST</h1>
<button id="btn">add image</button>  

<div id="imgdiv"><a href="./test-1.jpg" data-lightbox="album"><img src="./test-1.jpg" width="100"></a></div>
</body>
</html>

「add image」をクリックする前の表示は以下の通りです。

「add image」を2回クリックした後の表示は以下の様になります。

画像のどれかをクリックすると「lightbox2」が機能し、画像の拡大が行われます。

JQuery でのセレクタに対する append メソッドで上手くいくのか懸念していましたが、今回のテストでは特に問題なく「lightbox2」は動作しました。

関連する記事

⇒JavaScript 何に使う
⇒JavaScript jQueryの使い方（セレクタ）
⇒JavaScript jQueryの使い方（セレクタ）その２
⇒JavaScript 関数の宣言について(function)
⇒JavaScript jQueryでJavascriptファイルの動的に変更する方法
⇒JavaScript jQueryを使った checkbox の操作方法