PostgreSQLとMySQLのよく使うコマンド対応表

MySQL
スポンサーリンク

PostgreSQLとMySQLで、よく調べるコマンドを並べた対応表です。表のコマンドは、どちらも実際に動かして確認しました。後半に、確認中に引っかかった点をまとめています。

載せるコマンドは、Googleの検索候補によく出てくるもの(テーブル一覧、バージョン確認、ユーザー作成など)から選びました。

結論

接続・実行

やりたいことPostgreSQLMySQL
接続するpsql -h localhost -U postgres -d appmysql -h 127.0.0.1 -u root -p app
SQLを1つ実行して終わるpsql -U postgres -d app -c "SELECT 1;"mysql -u root -p app -e "SELECT 1;"
SQLファイルを実行するpsql -U postgres -d app -f query.sqlmysql -u root -p app < query.sql
データベースを切り替える\c appUSE app;

確認

やりたいことPostgreSQLMySQL
サーバーのバージョンSELECT version();SELECT VERSION();
クライアントのバージョンpsql --versionmysql --version
今のデータベースとユーザーSELECT current_database(), current_user;SELECT DATABASE(), CURRENT_USER();
設定値SHOW max_connections;SHOW VARIABLES LIKE 'max_connections';
接続数SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend';SHOW STATUS LIKE 'Threads_connected';

一覧

やりたいことPostgreSQLMySQL
データベース一覧\lSHOW DATABASES;
テーブル一覧\dtSHOW TABLES;
テーブル一覧(SQLで)SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';SELECT table_name FROM information_schema.tables WHERE table_schema = 'app';
スキーマ一覧\dnなし(MySQLではスキーマとデータベースが同じ意味)
テーブルの定義\d usersDESCRIBE users;
インデックス一覧\di(検索パス上のインデックス)SHOW INDEX FROM users;(指定したテーブル)
ユーザー一覧\duSELECT user, host FROM mysql.user;

作成・変更・削除

やりたいことPostgreSQLMySQL
データベースを作るCREATE DATABASE tmp_db;CREATE DATABASE tmp_db;
データベースを削除するDROP DATABASE tmp_db;DROP DATABASE tmp_db;
テーブル名を変えるALTER TABLE users RENAME TO members;RENAME TABLE users TO members;
カラム名を変えるALTER TABLE users RENAME COLUMN name TO full_name;ALTER TABLE users RENAME COLUMN name TO full_name;
カラムの型を変えるALTER TABLE users ALTER COLUMN name TYPE varchar(200);ALTER TABLE users MODIFY name varchar(200) NOT NULL DEFAULT '';

ユーザーと権限

やりたいことPostgreSQLMySQL
ユーザーを作るCREATE USER app_user WITH PASSWORD 'secret';CREATE USER 'app_user'@'%' IDENTIFIED BY 'secret';
パスワードを変えるALTER USER app_user WITH PASSWORD 'secret2';ALTER USER 'app_user'@'%' IDENTIFIED BY 'secret2';
権限を付けるGRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO app_user;GRANT SELECT, INSERT ON app.* TO 'app_user'@'%';
権限を確認する\dp users(指定したテーブルの権限)SHOW GRANTS FOR 'app_user'@'%';(指定したユーザーの権限)
権限を取り消すREVOKE INSERT ON ALL TABLES IN SCHEMA public FROM app_user;REVOKE INSERT ON app.* FROM 'app_user'@'%';
ユーザーを削除するDROP OWNED BY app_user;
DROP USER app_user;		DROP USER 'app_user'@'%';

エクスポート・インポート

やりたいことPostgreSQLMySQL
ダンプを取るpg_dump -U postgres app > app.sqlmysqldump -u root -p --single-transaction --set-gtid-purged=OFF app > app.sql
リストアする(先にデータベースを作る)psql -q -U postgres -d app2 -f app.sqlmysql -u root -p app2 < app.sql
CSVに出力する\copy users TO 'users.csv' CSV HEADERSELECT * FROM users INTO OUTFILE '/var/lib/mysql-files/users.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"';
CSVを取り込む\copy users_import FROM 'users.csv' CSV HEADERLOAD DATA INFILE '/var/lib/mysql-files/users.csv' INTO TABLE users_import FIELDS TERMINATED BY ',' ENCLOSED BY '"';
  • \ で始まるコマンドは、psqlが処理するコマンドで、SQLではありません。アプリケーションなどから一覧を取るときは、information_schema を使うSQLのほうを使います。
  • PostgreSQLの \copy は、psqlを実行している側のファイルを読み書きします。MySQLの INTO OUTFILE と LOAD DATA INFILE は、サーバー側のファイルを読み書きします(後述)。
  • データベース名、ユーザー名、パスワード(app、app_user、secret など)は、確認用の例です。ファイル(query.sql、app.sql、users.csv)は、確認では /tmp に置きました。CSVの取り込み先の users_import は、users と同じ形で先に作っておきます(PostgreSQLは CREATE TABLE users_import (LIKE users);、MySQLは CREATE TABLE users_import LIKE users;)。
  • PostgreSQLで、IDが serial のテーブルに別のユーザーでINSERTするには、テーブルの権限に加えてシーケンスの権限も必要です(後述)。
  • ダンプとリストアのコマンドは、データベースのコンテナの中で実行する前提です。自分のPC(Windows、Mac)のシェルから実行するときは、OSによって結果が変わることがあります(後述)。

動作確認した環境

  • PostgreSQL 18.6(Dockerイメージ postgres:18.6)
  • MySQL 9.7.4(Dockerイメージ mysql:9.7.4。長期サポートのLTS版)
  • Docker 29.7.2(Windows 11 の Docker Desktop)
  • 自分のPCのシェルの章だけ: PowerShell 7.6、Windows PowerShell 5.1、Linuxのsh(docker:29.7.2-cli)
  • 確認日: 2026年11月5日

この記事は、MacとWindowsの両方の読者を対象にしています。データベースはDockerのコンテナで動かし、コマンドもコンテナの中で実行します。確認はWindows 11のDocker Desktop(Linuxコンテナの設定)で行いました。Macでは確認していませんが、使うのは docker のコマンドとコンテナの中のコマンドだけなので、同じ手順で動く想定です。OSで違いが出たのは、自分のPCのシェルでダンプをファイルに書くときでした(「ハマりやすい点」の最後の章)。

この記事のコマンドは、Claude(AI)がDockerのコンテナで実行して確認しました。MySQLは、新しいInnovation版の26.10.0(mysql:26.10.0)でも同じ手順を実行し、バージョンの表示以外は同じ結果になりました。WindowsやmacOSに直接入れたデータベースでは確認していません。

準備

同じ環境は、次のコマンドで用意できます。起動が終わるまで少し待ってから接続します。

$ docker run -d --name pg -e POSTGRES_PASSWORD=pass postgres:18.6
$ docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=pass mysql:9.7.4

確認用のデータベースとテーブルを作ります。

$ docker exec -it pg psql -U postgres
postgres=# CREATE DATABASE app;
postgres=# \c app
app=# CREATE TABLE users (id serial PRIMARY KEY, name varchar(50) NOT NULL DEFAULT '', email varchar(100) UNIQUE);
app=# INSERT INTO users (name, email) VALUES ('alice', 'alice@example.com'), ('bob', 'bob@example.com');
$ docker exec -it mysql mysql -u root -p
mysql> CREATE DATABASE app;
mysql> USE app;
mysql> CREATE TABLE users (id int AUTO_INCREMENT PRIMARY KEY, name varchar(50) NOT NULL DEFAULT '', email varchar(100) UNIQUE);
mysql> INSERT INTO users (name, email) VALUES ('alice', 'alice@example.com'), ('bob', 'bob@example.com');

別に確かめたと書いた例を除き、以降は準備のあとに上から順に実行した結果です。表記について、次の点に注意してください。

  • プロンプトは、シェルを $、psqlを app=#、mysqlを mysql> で表しています。シェルのコマンドは、コンテナの中(docker exec -it pg sh など)で実行しました。
  • 確認では、SQLを psql -c と mysql -e で渡して実行しました。MySQLの出力には、対話モードで出る Query OK や 1 row in set の行がありません。
  • パスワードは確認では環境変数で渡しました。-p をつけて実行すると、パスワードを聞かれます。

出力の例

テーブル一覧です。

app=# \dt
          List of tables
 Schema | Name  | Type  |  Owner
--------+-------+-------+----------
 public | users | table | postgres
(1 row)
mysql> SHOW TABLES;
+---------------+
| Tables_in_app |
+---------------+
| users         |
+---------------+

テーブルの定義です。PostgreSQLの \d は、インデックスもいっしょに表示します。

app=# \d users
                                    Table "public.users"
 Column |          Type          | Collation | Nullable |              Default
--------+------------------------+-----------+----------+-----------------------------------
 id     | integer                |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying(50)  |           | not null | ''::character varying
 email  | character varying(100) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
mysql> DESCRIBE users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)  | NO   |     |         |                |
| email | varchar(100) | YES  | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

ハマりやすい点

MySQL 9.4以降、-e では \G や source が使えない

結果を縦に表示するには、PostgreSQLでは \x、MySQLでは文末の \G を使います。

app=# \x on
Expanded display is on.
app=# SELECT * FROM users WHERE id = 1;
-[ RECORD 1 ]------------
id    | 1
name  | alice
email | alice@example.com

\x on は、psqlを終了するか \x off を実行するまで続きます。確認では、この例だけを1回のpsqlで実行したので、以降の例は縦表示になっていません。

MySQLでは、あとで使うSQLファイルを先に作っておきます(MySQLのコンテナの中で実行)。

$ echo "SELECT count(*) FROM users;" > /tmp/query.sql

MySQLの \G は、-e で渡すとエラーになりました。ファイルを実行する source も使えず、SQLとしてサーバーに送られて構文エラーになりました。MySQL 9.4.0で、mysqlクライアントの \G や source などのコマンドを有効にする --commands オプションが追加され、既定で無効になりました(リリースノート)。--commands をつけるか、縦表示なら -E オプションを使います。

$ mysql -u root -p app -e "SELECT * FROM users WHERE id = 1\G"
ERROR at line 1: Unknown command '\G'.
$ mysql -u root -p app -e "source /tmp/query.sql"
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'source /tmp/query.sql' at line 1
$ mysql -u root -p --commands app -e "SELECT * FROM users WHERE id = 1\G"
*************************** 1. row ***************************
   id: 1
 name: alice
email: alice@example.com
$ mysql -u root -p -E app -e "SELECT * FROM users WHERE id = 1"
*************************** 1. row ***************************
   id: 1
 name: alice
email: alice@example.com

対話モードでは、--commands をつけなくても \G と source が使えました。

mysql> SELECT * FROM users WHERE id = 1\G
*************************** 1. row ***************************
   id: 1
 name: alice
email: alice@example.com
1 row in set (0.000 sec)

mysql> source /tmp/query.sql
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.000 sec)

MySQLで localhost を指定すると接続できないことがある

Unix系の環境では、mysqlクライアントに localhost を指定すると、TCPではなくソケットファイルで接続します(公式ドキュメント)。ポートにはつながるのに、ソケットファイルがない環境では接続できません。

ここでは、サーバーのコンテナとネットワークだけを共有する別のコンテナから接続して確かめました。コマンドはホストで実行します。127.0.0.1 を指定するか、--protocol=TCP をつけると接続できました。

$ docker run --rm --network container:mysql -e MYSQL_PWD=pass mysql:9.7.4 mysql -h localhost -u root -e "SELECT 1;"
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
$ docker run --rm --network container:mysql -e MYSQL_PWD=pass mysql:9.7.4 mysql -h 127.0.0.1 -u root -e "SELECT 1;"
1
1
$ docker run --rm --network container:mysql -e MYSQL_PWD=pass mysql:9.7.4 mysql -h localhost --protocol=TCP -u root -e "SELECT 1;"
1
1

PostgreSQLでは、同じ条件で -h localhost を指定してもTCPで接続できました。

$ docker run --rm --network container:pg -e PGPASSWORD=pass postgres:18.6 psql -h localhost -U postgres -c "SELECT 1;"
 ?column?
----------
        1
(1 row)

pg_stat_activity の件数が、接続数より多い

pg_stat_activity には、PostgreSQLのバックグラウンドのプロセスも入っています。接続しているのは自分だけでしたが、件数は9でした。接続数を数えるときは、backend_type = 'client backend' で絞り込みます。

app=# SELECT count(*) FROM pg_stat_activity;
 count
-------
     9
(1 row)

app=# SELECT backend_type, count(*) FROM pg_stat_activity GROUP BY backend_type ORDER BY backend_type;
         backend_type         | count
------------------------------+-------
 autovacuum launcher          |     1
 background writer            |     1
 checkpointer                 |     1
 client backend               |     1
 io worker                    |     3
 logical replication launcher |     1
 walwriter                    |     1
(7 rows)

MySQLの MODIFY で、NOT NULL と DEFAULT が消える

MySQLの MODIFY で型だけを書くと、元の NOT NULL と DEFAULT が消えました。公式ドキュメントにも、新しい定義に書かなかった属性は引き継がれないとあります(PRIMARY KEY や UNIQUE などのインデックスの属性は別扱いです)。

mysql> ALTER TABLE users MODIFY name varchar(100);
mysql> DESCRIBE users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
| email | varchar(100) | YES  | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

残したい NOT NULL や DEFAULT は、型といっしょにもう一度書きます。

mysql> ALTER TABLE users MODIFY name varchar(100) NOT NULL DEFAULT '';
mysql> DESCRIBE users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     |         |                |
| email | varchar(100) | YES  | UNI | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

PostgreSQLの ALTER COLUMN ... TYPE は型だけを変えるので、not null と既定値はそのまま残りました。

app=# ALTER TABLE users ALTER COLUMN name TYPE varchar(100);
ALTER TABLE
app=# \d users
                                    Table "public.users"
 Column |          Type          | Collation | Nullable |              Default
--------+------------------------+-----------+----------+-----------------------------------
 id     | integer                |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying(100) |           | not null | ''::character varying
 email  | character varying(100) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)

テーブル名の大文字と小文字

PostgreSQLは、ダブルクォートで囲まない名前を小文字として扱います。"Item" のように大文字を含めて作ったテーブルは、毎回ダブルクォートで囲む必要があります。

app=# CREATE TABLE "Item" (id int);
CREATE TABLE
app=# SELECT * FROM Item;
ERROR:  relation "item" does not exist
LINE 1: SELECT * FROM Item;
                      ^
app=# SELECT * FROM "Item";
 id
----
(0 rows)

MySQLは逆で、今回のLinuxの環境ではテーブル名の大文字と小文字が区別されました。設定値 lower_case_table_names の既定値は、Unix系が0、Windowsが1、macOSが2です(公式ドキュメント)。WindowsやmacOSで作ったSQLをLinuxで動かすと、エラーになることがあります。

mysql> CREATE TABLE Item (id int);
mysql> SELECT * FROM item;
ERROR 1146 (42S02) at line 1: Table 'app.item' doesn't exist
mysql> SELECT * FROM Item;
mysql> SHOW VARIABLES LIKE 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+

PostgreSQLの GRANT … ON ALL TABLES は、あとから作ったテーブルに効かない

ON ALL TABLES IN SCHEMA は、実行した時点にあるテーブルだけが対象です。あとから作った posts は読めませんでした。ここでは、app_user での接続をシェルから行っています。

app=# CREATE USER app_user WITH PASSWORD 'secret';
CREATE ROLE
app=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;
GRANT
app=# CREATE TABLE posts (id int);
CREATE TABLE

$ psql -h localhost -U app_user -d app -c "SELECT * FROM posts;"
ERROR:  permission denied for table posts

これから作るテーブルにも権限を付けるには、ALTER DEFAULT PRIVILEGES を使います。設定のあとに作った comments は読めました。既存のテーブルには効かないので(公式ドキュメント)、posts は読めないままです。既存のテーブルには、もう一度 GRANT ... ON ALL TABLES を実行します。

また、FOR ROLE を省略した ALTER DEFAULT PRIVILEGES は、実行したユーザーがこれから作るテーブルに効きます(公式ドキュメント)。ここでは、設定もテーブルの作成も postgres で行っています。マイグレーション用など別のユーザーがテーブルを作る場合は、そのユーザーについて設定します。

app=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES
app=# CREATE TABLE comments (id int);
CREATE TABLE

$ psql -h localhost -U app_user -d app -c "SELECT * FROM comments;"
 id
----
(0 rows)

$ psql -h localhost -U app_user -d app -c "SELECT * FROM posts;"
ERROR:  permission denied for table posts

MySQLの GRANT ... ON app.* は、データベース単位の権限です。あとから作ったテーブルもエラーにならずに読めました(テーブルが空なので、出力はありません)。

mysql> CREATE USER 'app_user'@'%' IDENTIFIED BY 'secret';
mysql> GRANT SELECT ON app.* TO 'app_user'@'%';
mysql> CREATE TABLE posts (id int);

$ mysql -h 127.0.0.1 -u app_user -p app -e "SELECT * FROM posts;"

PostgreSQLで、INSERT権限を付けたのにINSERTできない

IDが serial のテーブルでは、IDの連番をシーケンスが出しています。テーブルの INSERT 権限だけでは、シーケンスを使えずにエラーになりました。テーブルへの GRANT はシーケンスに及ばないので(公式ドキュメント)、シーケンスにも権限を付けます。

この例は、通しの流れとは別に、新しいコンテナで準備と同じ users を作って確かめました。

app=# CREATE USER app_user WITH PASSWORD 'secret';
CREATE ROLE
app=# GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO app_user;
GRANT

$ psql -h localhost -U app_user -d app -c "INSERT INTO users (name, email) VALUES ('carol', 'carol@example.com');"
ERROR:  permission denied for sequence users_id_seq

app=# GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;
GRANT

$ psql -h localhost -U app_user -d app -c "INSERT INTO users (name, email) VALUES ('carol', 'carol@example.com');"
INSERT 0 1

PostgreSQLで、権限が残っているユーザーを削除できない

権限が付いたままのユーザーは、DROP USER でエラーになりました。DROP OWNED BY で権限を外してから削除します。

app=# DROP USER app_user;
ERROR:  role "app_user" cannot be dropped because some objects depend on it
DETAIL:  privileges for table users
privileges for table "Item"
privileges for default privileges on new relations belonging to role postgres in schema public
privileges for table comments
app=# DROP OWNED BY app_user;
DROP OWNED
app=# DROP USER app_user;
DROP ROLE

DROP OWNED BY は、そのユーザーが所有するテーブルなども削除します。残したいものがあれば、先に REASSIGN OWNED BY で所有者を移します。また、別のデータベースの中にあるテーブルなどの所有物と権限は処理されません(公式ドキュメント)。別のデータベースのテーブルにも権限があったときは、そちらに接続して DROP OWNED BY を実行するまで、DROP USER はエラーになりました。

MySQLの DROP USER 'app_user'@'%'; は、権限が付いたままでもエラーになりませんでした。

リストアの前にデータベースを作る。MySQLは同じサーバーでGTIDのエラーになる

表のダンプのコマンドでは、リストア先のデータベースは作られません。先に CREATE DATABASE を実行します。

$ pg_dump -U postgres app > app.sql
$ psql -q -U postgres -d app2 -f app.sql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "app2" does not exist

MySQLは、オプションなしの mysqldump で警告が出ました。データベースを作ってからリストアすると、今度はGTIDのエラーになりました。今回のイメージでは gtid_mode が ON で、ダンプに含まれるGTIDの情報が、同じサーバーの実行済みのGTIDと重なったためです。

$ mysqldump -u root -p app > app.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump.
In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --source-data.
$ mysql -u root -p app2 < app.sql
ERROR 1049 (42000): Unknown database 'app2'

mysql> CREATE DATABASE app2;

$ mysql -u root -p app2 < app.sql
ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

mysql> SELECT @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON          |
+-------------+

警告にあるとおり、--set-gtid-purged=OFF でGTIDの情報を含めずにダンプすると、リストアできました。--single-transaction は、警告にある一貫性のためのオプションです。

$ mysqldump -u root -p --single-transaction --set-gtid-purged=OFF app > app.sql
$ mysql -u root -p app2 < app.sql
$ mysql -u root -p -t app2 -e "SELECT * FROM users;"
+----+-------+-------------------+
| id | name  | email             |
+----+-------+-------------------+
|  1 | alice | alice@example.com |
|  2 | bob   | bob@example.com   |
+----+-------+-------------------+

MySQLのCSVの出力と取り込みには制限がある

INTO OUTFILE は、設定値 secure_file_priv のディレクトリにしか書き込めませんでした。今回のイメージでは /var/lib/mysql-files/ です。ファイルはサーバー側にでき、見出しの行は付きません。

mysql> SELECT * FROM users INTO OUTFILE '/tmp/users.csv';
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

クライアント側にファイルを作りたいときは、-B オプションで出力をリダイレクトする方法もあります。この場合はCSVではなく、見出しつきのタブ区切りになります。

$ mysql -u root -p app -B -e "SELECT * FROM users;"
id	name	email
1	alice	alice@example.com
2	bob	bob@example.com

クライアント側のファイルを取り込む LOAD DATA LOCAL INFILE は、サーバーとクライアントの両方で許可しないとエラーになります。サーバーの local_infile は既定で無効です(公式ドキュメント)。

$ printf "id,name,email\n3,carol,carol@example.com\n" > /tmp/new.csv

mysql> LOAD DATA LOCAL INFILE '/tmp/new.csv' INTO TABLE users FIELDS TERMINATED BY ',' IGNORE 1 LINES;
ERROR 3948 (42000) at line 1: Loading local data is disabled; this must be enabled on both the client and server sides
mysql> SHOW VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+

サーバーで local_infile を有効にし、クライアントに --local-infile=1 をつけると取り込めました。サーバーの設定を変えるので、使い終わったら元に戻すことを検討してください。

mysql> SET GLOBAL local_infile = ON;

$ mysql -u root -p --local-infile=1 app -e "LOAD DATA LOCAL INFILE '/tmp/new.csv' INTO TABLE users FIELDS TERMINATED BY ',' IGNORE 1 LINES;"

mysql> SELECT * FROM users;
+----+-------+-------------------+
| id | name  | email             |
+----+-------+-------------------+
|  1 | alice | alice@example.com |
|  2 | bob   | bob@example.com   |
|  3 | carol | carol@example.com |
+----+-------+-------------------+

自分のPCのシェルでダンプを取ると、Windows PowerShell 5.1では文字コードが変わる

この章は、通しの流れとは別に、新しいコンテナを用意して確かめました。データには、日本語の名前(山田)の行を1行足しています。コマンドは、Windows 11 のPowerShell 7.6、Windows PowerShell 5.1 と、比較のためにLinuxのsh(Docker CLIの公式イメージ docker:29.7.2-cli、Alpine Linux)から実行しました。Macのターミナル(zsh)では確認していません。

コンテナの外のシェルで、ダンプの出力をリダイレクトしてファイルに書きます。ファイル名は、シェルごとに分けています。

PS> docker exec pg pg_dump -U postgres app > pg-ps5.1.sql

Windows PowerShell 5.1 で書いたファイルは、先頭が FF FE のUTF-16になり、日本語の名前もUTF-8のバイト列ではなくなりました。mysqldump でも同じでした。PowerShell 7.6 とLinuxのシェルで書いたファイルは、UTF-8のままでした。

UTF-16のファイルをコンテナにコピーしてリストアすると、エラーになりました。このとき、psqlの終了コードは0でした。エラーの表示を見落とさないように注意が必要です。

PS> docker exec pg psql -U postgres -c "CREATE DATABASE app_ps51;"
CREATE DATABASE
PS> docker cp pg-ps5.1.sql pg:/tmp/ps51.sql
PS> docker exec pg psql -q -U postgres -d app_ps51 -f /tmp/ps51.sql
psql:/tmp/ps51.sql:1: ERROR:  invalid byte sequence for encoding "UTF8": 0xff

また、PowerShellでは入力のリダイレクトの < が使えず、The '<' operator is reserved for future use. というエラーになりました(PowerShell 7.6 で確認)。

OSやシェルによらない方法は、コンテナの中でファイルに書き、docker cp で取り出すことです。リストアも、ファイルを docker cp でコンテナに入れてから、コンテナの中で読み込みます。docker cp は、コンテナと自分のPCの間でファイルをコピーするDockerのコマンドです(公式ドキュメント)。次のコマンドは、3つのシェルのどれでも同じ結果になり、日本語の名前もそのまま戻りました。Macのターミナルでも同じコマンドを使う想定ですが、確認はしていません。

$ docker exec pg pg_dump -U postgres -f /tmp/app.sql app
$ docker cp pg:/tmp/app.sql app.sql
$ docker exec pg psql -U postgres -c "CREATE DATABASE app2;"
CREATE DATABASE
$ docker cp app.sql pg:/tmp/restore.sql
$ docker exec pg psql -q -U postgres -d app2 -f /tmp/restore.sql
 set_config
------------

(1 row)

 setval
--------
      3
(1 row)
$ docker exec pg psql -U postgres -d app2 -c "SELECT id, encode(convert_to(name, 'UTF8'), 'hex') AS name_hex FROM users ORDER BY id;"
 id |   name_hex
----+--------------
  1 | 616c696365
  2 | 626f62
  3 | e5b1b1e794b0
(3 rows)

MySQLでは、--result-file でコンテナの中のファイルに書きます。リストアの < は、sh -c "..." の中に書けば、コンテナの中のシェルが処理するので、PowerShellでも使えました。

$ docker exec -e MYSQL_PWD=pass mysql mysqldump -u root --single-transaction --set-gtid-purged=OFF --result-file=/tmp/app.sql app
$ docker cp mysql:/tmp/app.sql app-mysql.sql
$ docker exec -e MYSQL_PWD=pass mysql mysql -u root -e "CREATE DATABASE app2;"
$ docker cp app-mysql.sql mysql:/tmp/restore.sql
$ docker exec -e MYSQL_PWD=pass mysql sh -c "mysql -u root app2 < /tmp/restore.sql"
$ docker exec -e MYSQL_PWD=pass mysql mysql -u root -t app2 -e "SELECT id, HEX(name) AS name_hex FROM users ORDER BY id;"
+----+--------------+
| id | name_hex     |
+----+--------------+
|  1 | 616C696365   |
|  2 | 626F62       |
|  3 | E5B1B1E794B0 |
+----+--------------+

e5b1b1e794b0 は「山田」をUTF-8で表したバイト列です。表示の文字化けと区別するため、16進数で確かめました。

参考

スポンサーリンク
MySQLPostgreSQLデータベース
スポンサーリンク
シェアする
hayoをフォローする
暇なのでいろいろと…
スポンサーリンク

コメント

タイトルとURLをコピーしました