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

2025/02/03

[PR]

×

[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。

CM

2024/01/17

MySQL でも日付型のデータを各種フォーマット変換を行って表示することがよくあります。
今回はその変換を行う DATE_FORMAT 関数の使い方について説明します。

DATE_FORMAT 関数は以下の様な引数をとります。

■[DATE_FORMAT]の引数について

DATE_FORMAT(date_data, format_string)
--   date_data     : 変換を行う日付データ
--   format_string : 変換指定文字列('%Y', '%m', '%d' ... などの文字列)

DATE_FORMAT 関数を使った簡単な例のSQLを以下に示します。

MariaDB [(none)]> SELECT DATE_FORMAT(NOW(), '%Y/%m/%d');
+--------------------------------+
| DATE_FORMAT(NOW(), '%Y/%m/%d') |
+--------------------------------+
| 2024/01/17                     |
+--------------------------------+
1 row in set (0.48 sec)

変換指定文字列として利用できるものは以下の指定子があります。

指定子	説明
%a	簡略曜日名 (Sun..Sat)
%b	簡略月名 (Jan..Dec)
%c	月、数字 (0..12)
%D	英語のサフィクスを持つ日付 (0th, 1st, 2nd, 3rd, …)
%d	日、数字 (00..31)
%e	日、数字 (0..31)
%f	マイクロ秒 (000000..999999)
%H	時間 (00..23)
%h	時間 (01..12)
%I	時間 (01..12)
%i	分、数字 (00..59)
%j	年間通算日 (001..366)
%k	時 (0..23)
%l	時 (1..12)
%M	月名 (January..December)
%m	月、数字 (00..12)
%p	AM または PM
%r	時間、12 時間単位 (hh:mm:ss に AM または PM が続く)
%S	秒数 (0059)
%s	秒数 (0059)
%T	時間、24 時間単位 (hh:mm:ss)
%U	週 (00..53)、日曜日が週の初日、WEEK() モード 0
%u	週 (00..53)、月曜日が週の初日、WEEK() モード 1
%V	週 (01..53)、日曜日が週の初日、WEEK() モード 2、%X とともに使用
%v	週 (01..53)、月曜日が週の初日、WEEK() モード 3、%x とともに使用
%W	曜日名 (Sunday..Saturday)
%w	曜日 (0=Sunday..6=Saturday)
%X	年間の週、日曜日が週の初日、数字、4 桁、%V とともに使用
%x	年間の週、月曜日が週の初日、数字、4 桁、%v とともに使用
%Y	年、数字、4 桁
%y	年、数字 (2 桁)
%%	リテラル % 文字
%x	x (上記にないすべての「x」)

いろんな指定子があるのですが、通常使うのは年月日の指定子と24時制の時刻の指定子ぐらいでしょうか。そこで以下に例を示します。

MariaDB [(none)]> SELECT
    ->     DATE_FORMAT(NOW(), '%Y/%m/%d')  as  'YYYY/mm/dd'
    ->    ,DATE_FORMAT(NOW(), '%y/%m/%d')  as  'yy/mm/dd'
    ->    ,DATE_FORMAT(NOW(), '%H:%i:%s')  as  'HH:MM:SS'
    ->    ,DATE_FORMAT(NOW(), '%Y年%m月%d日')  as 'YYYY年mm月dd日'
    ->    ,DATE_FORMAT(NOW(), '%H%時%i%分%s%秒') as 'HH時MM分SS秒'
    -> ;
+------------+----------+----------+----------------+--------------+
| YYYY/mm/dd | yy/mm/dd | HH:MM:SS | YYYY年mm月dd日 | HH時MM分SS秒 |
+------------+----------+----------+----------------+--------------+
| 2024/01/17 | 24/01/17 | 18:26:01 | 2024年01月17日 | 18時26分01秒 |
+------------+----------+----------+----------------+--------------+
1 row in set (0.00 sec)

PR

MySQL
0CM

2024/01/17

MySQL 仮想連番のテーブルを生成する方法について

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

仮想的に数千～数万件の連番を返すテーブルを取得する 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;

MySQL
0CM

2024/01/16

MySQL SELECTの出力に連番を付ける方法について

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
0CM

2024/01/16

MySQL ユーザー定義変数の宣言と使用方法について

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)

MySQL
0CM

2024/01/15

MySQL AUTO_INCREMENT カラムの自動生成値を取得するLAST_INSERT_ID()について

各種マスタやトランザクションテーブルの構造として、ユニークな主キーとなる 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]>

MySQL
0CM