Amazon RDSデータベースプレビュー環境でMariaDB 12.3の新機能を試してみた

MariaDB [(none)]> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 12.3.1-MariaDB |
+----------------+

MariaDB [(none)]> SELECT @@version_comment;
+----------------------------------------+
| @@version_comment                      |
+----------------------------------------+
| managed by https://aws.amazon.com/rds/ |
+----------------------------------------+

MariaDB [(none)]> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+

MariaDB [(none)]> SELECT '{"key": "value"}' IS JSON AS is_json;
+---------+
| is_json |
+---------+
|       1 |
+---------+

MariaDB [(none)]> SELECT '[1, 2, 3]' IS JSON AS is_json;
+---------+
| is_json |
+---------+
|       1 |
+---------+

MariaDB [(none)]> SELECT '"hello"' IS JSON AS is_json;
+---------+
| is_json |
+---------+
|       1 |
+---------+

MariaDB [(none)]> SELECT '42' IS JSON AS is_json;
+---------+
| is_json |
+---------+
|       1 |
+---------+

MariaDB [(none)]> SELECT 'not json' IS JSON AS is_json;
+---------+
| is_json |
+---------+
|       0 |
+---------+

MariaDB [(none)]> SELECT NULL IS JSON AS is_json;
+---------+
| is_json |
+---------+
|    NULL |
+---------+

MariaDB [testdb]> CREATE TABLE json_test (id INT AUTO_INCREMENT PRIMARY KEY, data TEXT);
MariaDB [testdb]> INSERT INTO json_test (data) VALUES
    ->   ('{"name": "Alice"}'),
    ->   ('invalid json'),
    ->   ('[1, 2, 3]'),
    ->   (NULL);

MariaDB [testdb]> SELECT id, data, data IS JSON AS is_valid_json FROM json_test;
+----+-------------------+---------------+
| id | data              | is_valid_json |
+----+-------------------+---------------+
|  1 | {"name": "Alice"} |             1 |
|  2 | invalid json      |             0 |
|  3 | [1, 2, 3]         |             1 |
|  4 | NULL              |          NULL |
+----+-------------------+---------------+

MariaDB [testdb]> SELECT id, data FROM json_test WHERE data IS JSON;
+----+-------------------+
| id | data              |
+----+-------------------+
|  1 | {"name": "Alice"} |
|  3 | [1, 2, 3]         |
+----+-------------------+

MariaDB [testdb]> SELECT TO_DATE('2026-06-16', 'YYYY-MM-DD') AS to_date_result;
+---------------------+
| to_date_result      |
+---------------------+
| 2026-06-16 00:00:00 |
+---------------------+

MariaDB [testdb]> SELECT TO_DATE('16/06/2026', 'DD/MM/YYYY') AS to_date_result;
+---------------------+
| to_date_result      |
+---------------------+
| 2026-06-16 00:00:00 |
+---------------------+

MariaDB [testdb]> SELECT TO_DATE('20260616', 'YYYYMMDD') AS to_date_result;
+---------------------+
| to_date_result      |
+---------------------+
| 2026-06-16 00:00:00 |
+---------------------+

MariaDB [testdb]> SELECT STR_TO_DATE('2026-06-16', '%Y-%m-%d') AS str_to_date_result;
+--------------------+
| str_to_date_result |
+--------------------+
| 2026-06-16         |
+--------------------+

MariaDB [testdb]> SELECT TO_DATE('2026-06-16', 'YYYY-MM-DD') AS to_date_result;
+---------------------+
| to_date_result      |
+---------------------+
| 2026-06-16 00:00:00 |
+---------------------+

MariaDB [testdb]> CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2), status VARCHAR(20));
MariaDB [testdb]> INSERT INTO orders VALUES
    ->   (1, 1, 1000, 'completed'),
    ->   (2, 1, 500, 'completed'),
    ->   (3, 2, 2000, 'pending'),
    ->   (4, 3, 100, 'completed'),
    ->   (5, 3, 50, 'completed');

MariaDB [testdb]> SELECT * FROM orders ORDER BY id;
+----+-------------+---------+-----------+
| id | customer_id | amount  | status    |
+----+-------------+---------+-----------+
|  1 |           1 | 1000.00 | completed |
|  2 |           1 |  500.00 | completed |
|  3 |           2 | 2000.00 | pending   |
|  4 |           3 |  100.00 | completed |
|  5 |           3 |   50.00 | completed |
+----+-------------+---------+-----------+

MariaDB [testdb]> WITH low_value_customers AS (
    ->   SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(amount) < 200
    -> )
    -> DELETE FROM orders WHERE customer_id IN (SELECT customer_id FROM low_value_customers);
Query OK, 2 rows affected (0.01 sec)

MariaDB [testdb]> SELECT * FROM orders ORDER BY id;
+----+-------------+---------+-----------+
| id | customer_id | amount  | status    |
+----+-------------+---------+-----------+
|  1 |           1 | 1000.00 | completed |
|  2 |           1 |  500.00 | completed |
|  3 |           2 | 2000.00 | pending   |
+----+-------------+---------+-----------+

MariaDB [testdb]> WITH high_value AS (
    ->   SELECT id FROM orders WHERE amount >= 1000
    -> )
    -> UPDATE orders SET status = 'vip' WHERE id IN (SELECT id FROM high_value);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [testdb]> SELECT * FROM orders ORDER BY id;
+----+-------------+---------+-----------+
| id | customer_id | amount  | status    |
+----+-------------+---------+-----------+
|  1 |           1 | 1000.00 | vip       |
|  2 |           1 |  500.00 | completed |
|  3 |           2 | 2000.00 | vip       |
+----+-------------+---------+-----------+