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

MySQL で文字列の文字数を半角、および全角（マルチバイト文字）の区別無く、1文字は1文字としてカウントしたい場合があります。
今回はその取得方法としての CHAR_LENGTH 関数の使い方について説明します。
なお、この関数に関連して LENGTH 関数がありますが、こちらは内部的な文字列のコードのバイト数を返します。 文字セットにより文字コードのバイト数は異なりますので、表面上は同じ文字列でも LENGTH が返す値はことる場合があります。

■[CHAR_LENGTH]の例

SELECT CHAR_LENGTH('あいう'), LENGTH('あいう');

この SQL を実行すると多分「3」と「6」が表示されるはずだと思っていたのですが、 Windowsのコマンドプロンプトから MySQL にログインして SQL を実行すると以下の結果となりました。

C:\xampp\mysql\bin>mysql -u root -p
Enter password: ****
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 329
Server version: 10.1.19-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT CHAR_LENGTH('あいう'), LENGTH('あいう');
+-----------------------+------------------+
| CHAR_LENGTH('あいう') | LENGTH('あいう') |
+-----------------------+------------------+
|                     6 |                6 |
+-----------------------+------------------+
1 row in set (0.00 sec)

SQL を実行すると「6」と「6」が表示されてしまいました。

いろいろネットで調べてみると、SQL を実行しているWindwosコマンドプロンプトと データベース側の文字セットが合っていないのではと思い調べてみました。
先ずは現在の文字セットがどうなっているのかを以下の SQL コマンドで調べます。

SHOW VARIABLES LIKE '%char%';

このコマンドの実行結果は以下の様になります。

MariaDB [(none)]> SHOW VARIABLES LIKE '%char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8                           |
| character_set_connection | utf8                           |
| character_set_database   | utf8                           |
| character_set_filesystem | binary                         |
| character_set_results    | utf8                           |
| character_set_server     | utf8                           |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.02 sec)

クライアント側の文字セット（ character_set_client ）が utf8 ではまずいのではないかと思い、 Windowsなので Shift-JIS の識別子である cp932 にしてみることにしました。

■クライアント側の文字セットの変更[SET NAMES]の例

この設定に便利なコマンドがあるのですが、以下のものです。

SET NAMES charset_name [COLLATE collation_name];
-- charset_name   :クライアントのセッションの文字セット
-- collation_name :照合順序

早速、このコマンドを使って cp932 にしてみることにしました。

MariaDB [(none)]> SET NAMES cp932;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | cp932                          |
| character_set_connection | cp932                          |
| character_set_database   | utf8                           |
| character_set_filesystem | binary                         |
| character_set_results    | cp932                          |
| character_set_server     | utf8                           |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

クライアント側の文字セット（ character_set_client ）が cp932 になりましたので CHAR_LENGTH のテストを行います。 期待通りに「3」の文字数が返されました。

MariaDB [(none)]> SELECT CHAR_LENGTH('あいう'), LENGTH('あいう');
+-----------------------+------------------+
| CHAR_LENGTH('あいう') | LENGTH('あいう') |
+-----------------------+------------------+
|                     3 |                6 |
+-----------------------+------------------+
1 row in set (0.00 sec)

なお phpMyAdmin で文字セットの確認と、上記の SQL を実行してみましたが、以下の様な表示となりました。

character_set_client が utf8mb4 となっているので 'あいう' のそれぞれのコードは内部的に3バイトのため、 LENGTH の結果は「9」が返ってきます。

MySQL では文字列型のデータを日付型のデータに変換を行うことがよくあります。
今回はその変換を行う STR_TO_DATE 関数の使い方について説明します。

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

■[STR_TO_DATE]の引数について

STR_TO_DATE(str_data, format_string)
--   str_data      : 変換を行う文字列データ
--   format_string : 変換書式文字列('%Y', '%m', '%d' ... などの文字列)

なお、この関数で戻される値は、書式文字列の指定方法により DATETIME値 DATE値 TIME値 の3種類があります。
書式文字列に日付と時間の両方の部分が含まれる場合は DATETIME 値を返し、文字列に日付と時間の部分の一方のみが含まれる場合は DATE または TIME 値を返します。

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

MariaDB [(none)]> SELECT STR_TO_DATE('2024/01/05', '%Y/%m/%d');
+---------------------------------------+
| STR_TO_DATE('2024/01/05', '%Y/%m/%d') |
+---------------------------------------+
| 2024-01-05                            |
+---------------------------------------+
1 row in set (0.10 sec)

このSQLの変換書式文字列は年月日の区切りに「/」（スラッシュ）を使っていますが、文字列の方がスラッシュでは無くハイフンにしたらどうなるでしょうか。
早速、試してみますと以下の様になります。結果的にNULLが返されてしまいますので、注意が必要です。（NULLが返されても問題無ければいいのですが）

MariaDB [(none)]> SELECT STR_TO_DATE('2024-01-05', '%Y/%m/%d');
+---------------------------------------+
| STR_TO_DATE('2024-01-05', '%Y/%m/%d') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

■[変換書式文字列]について

変換書式文字列として利用できるものは DATE_FORMAT 関数の時と同様に以下の指定子があります。

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

MariaDB [(none)]> SELECT
    ->      STR_TO_DATE('2024/01/05', '%Y/%m/%d') as 'DATE値'
    ->     ,STR_TO_DATE('24/01/05', '%y/%m/%d') as 'DATE値'
    ->     ,STR_TO_DATE('2024/01/05 16:25:34', '%Y/%m/%d %H:%i:%s') as 'DATETIME値'
    ->     ,STR_TO_DATE('16:25:34', '%H:%i:%s') as 'TIME値'
    ->     ,STR_TO_DATE('2024年01月10日', '%Y年%m月%d日') as 'DATE値'
    ->     ,STR_TO_DATE('2024年01月10日 18時05分29秒', '%Y年%m月%d日 %H時%i分%s秒') as 'DATETIME値'
    -> ;
+------------+------------+---------------------+----------+------------+---------------------+
| DATE値     | DATE値     | DATETIME値          | TIME値   | DATE値     | DATETIME値          |
+------------+------------+---------------------+----------+------------+---------------------+
| 2024-01-05 | 2024-01-05 | 2024-01-05 16:25:34 | 16:25:34 | 2024-01-10 | 2024-01-10 18:05:29 |
+------------+------------+---------------------+----------+------------+---------------------+
1 row in set (0.00 sec)

年の数字が2桁指定の場合は正しく「2024年」となりました。年のデータを2桁で扱うことは2000年問題の時の様なことが有りますので、4桁が普通かと思います。
なお、指定子の間に”年月日”などの全角を入れても正しく変換書式文字列を設定すれば変換できました。

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)

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

いろんな指定子があるのですが、通常使うのは年月日の指定子と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)

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

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