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

2025/01/18

[PR]

×

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

CM

2024/01/19

MySQL で文字列の連結を行う方法として CONCAT 関数があります。

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

CONCAT(str1, str2, str3, ...)
-- 文字列 str1, str2, str3 と指定されたものを連結した文字列を返し、
-- 引数に NULL が含まれている場合は NULL を返します。
-- なお、引数に数値が指定された場合は、文字列に変換されて連結されます。

オラクルでは CONCAT 関数の引数は2個までしかないのですが、MySQL では2個以上連結が可能です。（もっともオラクルでは「||」で連結すれば同じ事ができます）
それでは、CONCAT 関数の使用例を示します。

■[CONCAT関数]の例

MariaDB [(none)]> SELECT
    ->     CONCAT('ABC', 'DEFG', 'あいう', 'HI')   AS '文字列の連結'
    ->    ,CONCAT('ABC', 100, '全角')              AS 'リテラル数値を連結'
    ->    ,CONCAT(100, 200)                        AS 'リテラル数値のみを連結'
    ->    ,CONCAT('ABC', NULL, '全角')             AS 'NULLを連結'
    -> ;
+-----------------+--------------------+------------------------+------------+
| 文字列の連結    | リテラル数値を連結 | リテラル数値のみを連結 | NULLを連結 |
+-----------------+--------------------+------------------------+------------+
| ABCDEFGあいうHI | ABC100全角         | 100200                 | NULL       |
+-----------------+--------------------+------------------------+------------+
1 row in set (0.00 sec)

確かに数値の場合も文字列に変換されて処理されるのが分かります。
また、引数の中に NULL が存在する場合は NULL が返されます。

上記の例では、全てリテラルの文字列を引数に指定しましたが、リテラルであればそれらを単に羅列して SQL に記述すれば連結する様です。

■[文字列を並べて配置]の例

MariaDB [(none)]> SELECT 'ABC' 'DEFG' 'あいう' 'HI'   AS '文字列の連結';
+-----------------+
| 文字列の連結    |
+-----------------+
| ABCDEFGあいうHI |
+-----------------+
1 row in set (0.00 sec)

確かに文字列が連結されていますが、これにそこまでの有用性は無いかもしれません。

■[CONCAT関数]をテーブルデータを引数にする例

テーブルデータを引数に指定するために、データベースを指定し「t_auto」なるテストテーブルの一覧を取得します。

MariaDB [(none)]> USE test;
Database changed
MariaDB [test]> SELECT * FROM t_auto;
+----+--------+
| id | val    |
+----+--------+
|  1 | test1  |
|  2 | test2  |
|  4 | test4  |
|  5 | test-a |
|  6 | あいう |
+----+--------+
5 rows in set (0.00 sec)

それではこのテーブルの「id」と「val」の連結を一覧してみます。

MariaDB [test]> SELECT CONCAT(id, '-', val) FROM t_auto;
+----------------------+
| CONCAT(id, '-', val) |
+----------------------+
| 1-test1              |
| 2-test2              |
| 4-test4              |
| 5-test-a             |
| 6-あいう             |
+----------------------+
5 rows in set (0.00 sec)

テーブルデータの場合は上記の文字列の連続配置の様にはできなくて（オラクルの連結子「||」の様にはいかないので） CONCAT 関数の出番となります。

PR

MySQL
0CM

2024/01/19

MySQL 文字列から部分文字列を取得する方法について（LEFT、RIGHT、SUBSTRING）

MySQL で文字列から部分文字列を取得する方法として、 LEFT , RIGHT , SUBSTRING 関数があります。

LEFT , RIGHT 関数は指定された文字列の左側および右側から切出し文字数を指定し部分文字列を取得します。
また、SUBSTRING 関数は指定された文字列の切出し開始位置と切出し文字数を指定し、部分文字列を取得します。
これらの関数はマルチバイトセーフなので、全角文字も1文字として扱われます。
なお、 LEFT RIGHT 関数の説明は以下の通りです。

LEFT(str, len)
-- 文字列 str から左側から len 文字分の文字列を返し、引数が NULL である場合は NULL を返します。

RIGHT(str, len)
-- 文字列 str から右側から len 文字分の文字列を返し、引数が NULL である場合は NULL を返します。


-- ※SUBSTRINGは4つの形式があります
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)

-- len 引数を付けない形式では、位置 pos で始まる文字列 str からの部分文字列が返されます。
-- len 引数を付けた形式では、位置 pos で始まる文字列 str からの部分文字列 len 文字長が返されます。 
-- FROM を使用する形式は、標準の SQL 構文です。 
-- また、pos に負の値を使用することもできます。 
-- その場合、部分文字列の先頭は文字列の先頭でなく、文字列の末尾からの pos 文字になります。 
-- この関数のどの形式でも、pos で負の値を使用できます。 pos の値が 0 の場合、空の文字列が返されます。

-- （すべての形式の SUBSTRING() で、部分文字列の抽出が開始される文字列内の最初の文字の位置が 1 とみなされます。）

-- 「https://dev.mysql.com/doc/refman/8.0/ja/string-functions.html からの抜粋です。」

それでは順次、LEFT, RIGHT, SUBSTRING 関数の使用例を示します。

■[LEFT関数]の例

MariaDB [(none)]> SELECT
    ->     LEFT('ABCDEFGH', 2)
    ->    ,LEFT('ABCDEFGH', 10)
    ->    ,LEFT('日本語テスト', 3)
    ->    ,LEFT('日本語テスト', 10)
    ->    ,LEFT(NULL, 10)
    ->    ,LEFT('ABCDEFGH', 0)
    -> ;
+---------------------+----------------------+-------------------------+--------------------------+----------------+---------------------+
| LEFT('ABCDEFGH', 2) | LEFT('ABCDEFGH', 10) | LEFT('日本語テスト', 3) | LEFT('日本語テスト', 10) | LEFT(NULL, 10) | LEFT('ABCDEFGH', 0) |
+---------------------+----------------------+-------------------------+--------------------------+----------------+---------------------+
| AB                  | ABCDEFGH             | 日本語                  | 日本語テスト             | NULL           |                     |
+---------------------+----------------------+-------------------------+--------------------------+----------------+---------------------+
1 row in set (0.00 sec)

確かにマルチバイトの文字も1文字として処理されるのが分かります。
また、文字数が文字列より大きい値が指定されてた場合は指定文字列全てが返されます。
なお、文字数指定が「0」以下の場合は長さ「0」の文字列が返されるのが分かります。

■[RIGHT関数]の例

MariaDB [(none)]> SELECT
    ->     RIGHT('ABCDEFGH', 2)
    ->    ,RIGHT('ABCDEFGH', 10)
    ->    ,RIGHT('日本語テスト', 3)
    ->    ,RIGHT('日本語テスト', 10)
    ->    ,RIGHT(NULL, 10)
    ->    ,RIGHT('ABCDEFGH', 0)
    -> ;
+----------------------+-----------------------+--------------------------+---------------------------+-----------------+----------------------+
| RIGHT('ABCDEFGH', 2) | RIGHT('ABCDEFGH', 10) | RIGHT('日本語テスト', 3) | RIGHT('日本語テスト', 10) | RIGHT(NULL, 10) | RIGHT('ABCDEFGH',0)  |
+----------------------+-----------------------+--------------------------+---------------------------+-----------------+----------------------+
| GH                   | ABCDEFGH              | テスト                   | 日本語テスト              | NULL            |                      |
+----------------------+-----------------------+--------------------------+---------------------------+-----------------+----------------------+
1 row in set (0.02 sec)

確かに LEFT 関数の処理を右側から行った結果になっています。

■[SUBSTRING関数]の例

MariaDB [(none)]> SELECT
    ->     SUBSTRING('ABCDEFGHIJ', 6)          AS '先頭から6番目以降全て'
    ->    ,SUBSTRING('ABCDEFGHIJ' FROM 4)      AS '先頭から6番目以降全て'
    ->    ,SUBSTRING('ABCDEFGHIJ', 3, 6)       AS '先頭から3番目から6文字'
    ->    ,SUBSTRING('ABCDEFGHIJ', -3)         AS '最後から3番目以降全て'
    ->    ,SUBSTRING('ABCDEFGHIJ', -5, 3)      AS '最後から5番目から3文字'
    ->    ,SUBSTRING('ABCDEFGHIJ' FROM -4 FOR 2) AS '最後から4番目から2文字'
    -> ;
+-----------------------+-----------------------+------------------------+-----------------------+------------------------+------------------------+
| 先頭から6番目以降全て | 先頭から6番目以降全て | 先頭から3番目から6文字 | 最後から3番目以降全て | 最後から5番目から3文字 | 最後から4番目から2文字 |
+-----------------------+-----------------------+------------------------+-----------------------+------------------------+------------------------+
| FGHIJ                 | DEFGHIJ               | CDEFGH                 | HIJ                   | FGH                    | GH                     |
+-----------------------+-----------------------+------------------------+-----------------------+------------------------+------------------------+
1 row in set (0.00 sec)

MySQL
0CM

2024/01/19

MySQL 文字列の文字数の取得の注意点について（CHAR_LENGTH、LENGTH）

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

2024/01/18

MySQL 文字列から日付データへ変換する方法について（STR_TO_DATE）

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 関数の時と同様に以下の指定子があります。

指定子	説明
%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
    ->      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
0CM

2024/01/17

MySQL 日付データのフォーマット変換する方法について

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)

MySQL
0CM