よっしー
こんにちは。よっしーです(^^)
今日は、mysqlでUUIDのバイナリデータをロードしたときの方法についてご紹介します。
スポンサーリンク
背景
mysqlでUUIDをバイナリで管理しているテーブルがあり、そのテーブルに対して、LOAD DATAを使用したときのやり方を備忘として残しました。
検証環境の構築
検証環境として、Dockerを使用します。下記のファイルを用意しました。
├── compose.yml
└── mysql
├── Dockerfile
├── init.d
│ ├── 01.sql
│ └── 02.sql
└── mysql-filescompose.yml
version: '3.8'
services:
mysql:
build: ./mysql
container_name: mysql
user: mysql
volumes:
- ./mysql/mysql-files:/var/lib/mysql-files
- ./mysql/init.d:/docker-entrypoint-initdb.d
environment:
- MYSQL_DATABASE=test
- MYSQL_ROOT_PASSWORD=P@ssw0rd
ports:
- 3306:3306
mysql/Dockerfile
FROM mysql:8.1.0mysql/init.d/01.sql
USE test;
CREATE TABLE sample (
id BIGINT,
name VARCHAR(30),
uuid BINARY(16),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);mysql/init.d/02.sql
USE test;
INSERT INTO sample(id, name, uuid)
VALUES (1, 'name 01', UUID_TO_BIN(UUID()))
, (2, 'name 02', UUID_TO_BIN(UUID()))
, (3, 'name 03', UUID_TO_BIN(UUID()))
;動作確認
下記のコマンドを実行します。
docker compose build
docker compose up -dデータ確認
下記のコマンドを実行します。
docker compose exec -it mysql bash下記のコマンドを実行します。
mysql -uroot -p -Dtest -e "select * from sample;"下記のようにデータがあれば、構築成功です。
bash-4.4$ mysql -uroot -p -Dtest -e "select * from sample;"
Enter password:
+------+---------+------------------------------------+---------------------+---------------------+
| id | name | uuid | created_at | updated_at |
+------+---------+------------------------------------+---------------------+---------------------+
| 1 | name 01 | 0xCA2314F0436211EEA32B0242AC120002 | 2023-08-25 16:16:47 | 2023-08-25 16:16:47 |
| 2 | name 02 | 0xCA23179C436211EEA32B0242AC120002 | 2023-08-25 16:16:47 | 2023-08-25 16:16:47 |
| 3 | name 03 | 0xCA23181C436211EEA32B0242AC120002 | 2023-08-25 16:16:47 | 2023-08-25 16:16:47 |
+------+---------CSV出力
下記のコマンドを実行して、CSV出力します。
mysql -uroot -p -Dtest SELECT id, name, BIN_TO_UUID(uuid)
FROM sample
INTO OUTFILE '/var/lib/mysql-files/sample.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n';下記のコマンドを実行して、CSV出力されていれば成功です。
bash-4.4$ cat /var/lib/mysql-files/sample.csv
"1","name 01","ca2314f0-4362-11ee-a32b-0242ac120002"
"2","name 02","ca23179c-4362-11ee-a32b-0242ac120002"
"3","name 03","ca23181c-4362-11ee-a32b-0242ac120002"レコードクリア
下記のコマンドを実行して、レコードを削除します。
mysql -uroot -p -Dtest -e "truncate table sample;"下記のコマンドを実行して、レコードがないことを確認します。
mysql -uroot -p -Dtest -e "select * from sample;"CSVレコードのロード
下記のコマンドを実行します。
mysql -uroot -p -Dtest --enable-local-infileLOAD DATA
LOCAL
INFILE '/var/lib/mysql-files/sample.csv'
INTO TABLE sample
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@1,@2,@3)
SET id=@1, name=@2, uuid=UUID_TO_BIN(@3)
;下記の結果が出力されていれば成功です。
mysql> LOAD DATA
-> LOCAL
-> INFILE '/var/lib/mysql-files/sample.csv'
-> INTO TABLE sample
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> (@1,@2,@3)
-> SET id=@1, name=@2, uuid=UUID_TO_BIN(@3)
-> ;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0select してみると、レコードが登録されていることが確認できます。
mysql> select * from sample;
+------+---------+------------------------------------+---------------------+---------------------+
| id | name | uuid | created_at | updated_at |
+------+---------+------------------------------------+---------------------+---------------------+
| 1 | name 01 | 0xCA2314F0436211EEA32B0242AC120002 | 2023-08-25 16:54:44 | 2023-08-25 16:54:44 |
| 2 | name 02 | 0xCA23179C436211EEA32B0242AC120002 | 2023-08-25 16:54:44 | 2023-08-25 16:54:44 |
| 3 | name 03 | 0xCA23181C436211EEA32B0242AC120002 | 2023-08-25 16:54:44 | 2023-08-25 16:54:44 |
+------+---------+------------------------------------+---------------------+---------------------+
3 rows in set (0.01 sec)解説
INTO OUTFILE
このコードは、MySQLデータベース内のテーブルからデータを取得し、CSVファイルにエクスポートするためのSQLクエリです。クエリの内容を詳しく解説します。
SELECT id, name, BIN_TO_UUID(uuid):sampleテーブルからid、name、およびuuidカラムを取得するSELECT文です。BIN_TO_UUID(uuid)は、UUID形式のバイナリデータを読み取り、文字列のUUIDに変換するMySQLの関数です。FROM sample: データを取得する対象のテーブルを指定しています。ここでは「sample」というテーブルからデータを取得します。INTO OUTFILE '/var/lib/mysql-files/sample.csv': 取得したデータをCSVファイルに出力するためのステートメントです。sample.csvというファイルにデータを書き込みます。FIELDS TERMINATED BY ',': CSVファイル内のフィールド（カラム）をカンマで区切ることを示しています。ENCLOSED BY '"': フィールドがダブルクォーテーションで囲まれていることを示しています。ESCAPED BY '"': フィールド内のエスケープ文字としてダブルクォーテーションを使用することを示しています。これにより、フィールド内にエンクロージング文字（ダブルクォーテーション）やエスケープ文字が含まれる場合に適切に処理されます。LINES TERMINATED BY '\n': 行が改行文字(\n)で区切られていることを示しています。
このSQLクエリは、指定されたテーブルからデータを取得し、CSVファイルに書き込む操作を行います。CSVファイルのフィールド区切りやエンクロージング文字など、出力されるCSVファイルの形式が設定されています。データのエクスポートに際しては、ファイルシステム上の指定されたパスにCSVファイルが作成され、データが書き込まれます。
mysql -uroot -p -Dtest –enable-local-infile
このコードは、MySQLデータベースに対してコマンドラインから接続するためのコマンドです。以下に詳細な解説を提供します。
mysql: これはMySQLコマンドラインクライアントを起動するコマンドです。-uroot: これはMySQLに接続するためのユーザー名を指定しています。-uオプションの後に続く文字列がユーザー名です。ここでは「root」というユーザー名が指定されています。通常、rootはMySQLデータベースの管理者権限を持つユーザーです。-p: これはMySQLに接続する際にパスワードを入力するためのプロンプトを表示するオプションです。このオプションを指定することで、パスワードをコマンドラインに直接書くことなく、セキュリティを強化できます。コマンドを実行すると、パスワードを入力するためのプロンプトが表示されるでしょう。-Dtest: これは接続するデータベースの名前を指定しています。-Dオプションの後に続く文字列がデータベースの名前です。ここでは「test」という名前のデータベースに接続することを意味しています。--enable-local-infile: これはMySQLクライアントがLOAD DATA LOCAL INFILEステートメントを許可するためのオプションです。このステートメントを使用すると、ローカルファイルシステムからデータをデータベースにロードすることができます。このオプションを指定することで、この機能を有効にすることができます。
要するに、このコマンドは「root」ユーザーで「test」という名前のデータベースに接続し、ローカルファイルからデータをロードする際に必要な設定を有効にした状態でMySQLコマンドラインクライアントを起動するものです。
LOAD DATA
このコードは、MySQLデータベースのテーブルにCSVファイルからデータをロードするためのSQLクエリです。クエリの内容を詳しく解説します。
LOAD DATA LOCAL INFILE '/var/lib/mysql-files/sample2.csv': ローカルファイルシステムからデータをロードするためのステートメントです。sample2.csvというファイルからデータを読み込みます。LOCALキーワードは、クライアント側（実行しているコンピュータ側）のファイルシステムからファイルを読み込むことを意味します。INTO TABLE sample: ロードされたデータを挿入する対象のテーブルを指定しています。ここでは「sample」というテーブルにデータを挿入します。FIELDS TERMINATED BY ',': CSVファイル内のフィールド（カラム）がカンマで区切られていることを示しています。ENCLOSED BY '"': フィールドがダブルクォーテーションで囲まれていることを示しています。CSVファイル内のデータがダブルクォーテーションで囲まれている場合、それを処理するための指定です。LINES TERMINATED BY '\n': 行が改行文字(\n)で区切られていることを示しています。(@1,@2,@3): CSVファイル内のデータを一時的な変数（この場合は@1、@2、@3）に格納するための指定です。これにより、後続のSET句でこれらの変数を利用できます。SET id=@1, name=@2, uuid=UUID_TO_BIN(@3): テーブルのカラムに対して、ロードされたデータの一時変数を割り当てるための指定です。ここでは、idカラムに@1の値を、nameカラムに@2の値を、uuidカラムにはUUID_TO_BIN(@3)の評価結果（@3の値をバイナリのUUID形式に変換した値）を挿入します。
総括すると、このSQLクエリは指定されたCSVファイルからデータを読み込み、それをsampleテーブルに挿入する操作を行っています。CSVファイル内のデータ形式やテーブルのカラム構造に合わせて、適切な設定や変換を行いながらデータのロードが行われます。
おわりに
今日は、mysqlでUUIDのバイナリデータをロードしたときの方法についてご紹介しました。
よっしー
何か質問や相談があれば、コメントをお願いします。また、エンジニア案件の相談にも随時対応していますので、お気軽にお問い合わせください。
それでは、また明日お会いしましょう(^^)
コメント