MySQLではスキーマはデータベースと同義。所有者の概念がありません。同じデータベースに同じ名前のオブジェクトを作成する事は出来ません。
PostgreSQLではデータベースとスキーマは異なる。データベースには複数の名前付きスキーマが含まれ、異なるスキーマには同じデータベースでも同一名のオブジェクトを作成する事が可能。
■ PostgreSQLにおけるデータベースとスキーマ
確認①：確認の為に、postgresユーザー以外のユーザーを作成してDefaultスキーマを変更。
POC=# CREATE ROLE admin LOGIN;
CREATE ROLE
POC=# ALTER ROLE admin WITH PASSWORD 'password';
ALTER ROLE
POC=# ALTER ROLE admin SUPERUSER;
ALTER ROLE
POC=# ALTER ROLE admin SET search_path TO admin_schema, pg_catalog;
ALTER ROLE
POC=# CREATE SCHEMA admin_schema;
CREATE SCHEMA
確認②：以下の様に同じインスタンスに対して、異なったユーザーで接続して同じデータベース上にて、adminロールにてテーブルを作成してみます。結果としては、adminユーザースキーマ（admin_schema)にテーブルは作成されているのでpostgresユーザーからは見えません。
- adminセッション
POC=# select session_user,current_user,current_database(),current_schema();
session_user | current_user | current_database | current_schema
--------------+--------------+------------------+----------------
admin | admin | POC | admin_schema
(1 row)
POC=# create table t1 (c1 int,c2 text);
CREATE TABLE
POC=# \dt
List of relations
Schema | Name | Type | Owner
--------------+------+-------+-------
admin_schema | t1 | table | admin
(1 row)- postgresセッション
POC=# select session_user,current_user,current_database(),current_schema();
session_user | current_user | current_database | current_schema
--------------+--------------+------------------+----------------
postgres | postgres | POC | public
(1 row)
POC=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------------------+-------------------+----------
public | p12a-14_01 | table | postgres
public | people | table | postgres
public | sample_partition | partitioned table | postgres
public | sample_pm01 | table | postgres
確認③：同じデータベース上に同じ名前でテーブルを作成してデータを登録。
- adminセッション
POC=# select session_user,current_user,current_database(),current_schema();
session_user | current_user | current_database | current_schema
--------------+--------------+------------------+----------------
admin | admin | POC | admin_schema
(1 row)
POC=# \dt
List of relations
Schema | Name | Type | Owner
--------------+------+-------+-------
admin_schema | t1 | table | admin
(1 row)
POC=# insert into t1 values(1,to_char(now(), 'YYYY/MM/DD HH24:MI:SS')),(2,to_char(now(), 'YYYY/MM/DD HH24:MI:SS'));
INSERT 0 2
POC=# select * from t1;
c1 | c2
----+---------------------
1 | 2022/02/25 10:17:00
2 | 2022/02/25 10:17:00
(2 rows)- postgresセッション
POC=# select session_user,current_user,current_database(),current_schema();
session_user | current_user | current_database | current_schema
--------------+--------------+------------------+----------------
postgres | postgres | POC | public
(1 row)
POC=# create table t1 (c1 int,c2 text);
CREATE TABLE
POC=# \dt t1
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
POC=# insert into t1 values(1,to_char(now(), 'YYYY/MM/DD HH24:MI:SS')),(2,to_char(now(), 'YYYY/MM/DD HH24:MI:SS'));
INSERT 0 2
POC=# select * from t1;
c1 | c2
----+---------------------
1 | 2022/02/25 10:18:12
2 | 2022/02/25 10:18:12
(2 rows)
確認④：SETコマンドでのユーザーやsearch_pathを変更
Superuserであれば、以下の様に接続中にユーザーやサーチパスを変更する事が可能。
POC=# set session role postgres;
SET
POC=# select session_user,current_user,current_database(),current_schema();
session_user | current_user | current_database | current_schema
--------------+--------------+------------------+----------------
admin | postgres | POC | admin_schema
(1 row)
POC=# select * from t1;
c1 | c2
----+---------------------
1 | 2022/02/25 10:17:00
2 | 2022/02/25 10:17:00
(2 rows)
POC=# SET search_path TO public, pg_catalog;
SET
POC=# select * from t1;
c1 | c2
----+---------------------
1 | 2022/02/25 10:18:12
2 | 2022/02/25 10:18:12
(2 rows)
POC=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------------------+-------------------+----------
public | p12a-14_01 | table | postgres
public | people | table | postgres
public | t1 | table | postgres
public | trains | table | postgres
public | trains_backup | table | postgres
public | travel_y2022m01 | table | postgres
public | travel_y2022m02 | table | postgres
public | travel_y2022m03 | table | postgres
public | trip | table | postgres
public | trip_generated_column | table | postgres
(20 rows)
POC=# \q
~/git/rdbms-docker/postgresql [10:26:42]> psql -h 127.0.0.1 -U admin POC
Password for user admin:
psql (14.1 (Ubuntu 14.1-1.pgdg18.04+1), server 13.5 (Debian 13.5-1.pgdg110+1))
Type "help" for help.
POC=# select session_user,current_user,current_database(),current_schema();
session_user | current_user | current_database | current_schema
--------------+--------------+------------------+----------------
admin | admin | POC | admin_schema
(1 row)
POC=# select * from t1;
c1 | c2
----+---------------------
1 | 2022/02/25 10:17:00
2 | 2022/02/25 10:17:00
(2 rows)
POC=# \dt
List of relations
Schema | Name | Type | Owner
--------------+------+-------+-------
admin_schema | t1 | table | admin
(1 row)
POC=#- search_pathのみ変更
MySQLでデータベースを変更して、他のデータベース上の同じ名前のテーブルを参照する感じ。
POC=# select session_user,current_user,current_database(),current_schema();
session_user | current_user | current_database | current_schema
--------------+--------------+------------------+----------------
admin | admin | POC | admin_schema
(1 row)
POC=# select * from t1;
c1 | c2
----+---------------------
1 | 2022/02/25 10:17:00
2 | 2022/02/25 10:17:00
(2 rows)
POC=# \dt
List of relations
Schema | Name | Type | Owner
--------------+------+-------+-------
admin_schema | t1 | table | admin
(1 row)
POC=#
POC=#
POC=# select * from t1;
c1 | c2
----+---------------------
1 | 2022/02/25 10:17:00
2 | 2022/02/25 10:17:00
(2 rows)
POC=# SET search_path TO public, pg_catalog;
SET
POC=# select * from t1;
c1 | c2
----+---------------------
1 | 2022/02/25 10:18:12
2 | 2022/02/25 10:18:12
(2 rows)
■ MySQLにおけるデータベースとスキーマ
※ MySQLではDatabase = Schemaになります。同じデータベース(スキーマ)上には同じ名前のオブジェクトを作成する事は出来ません。
select
user() 'サーバーへの接続時に指定したユーザー名および接続元のクライアントホスト',
current_user() '現在のクライアントを認証する際に使用されたMySQLアカウント。このアカウントでアクセス権限が決まる。';
確認①：異なったユーザー間で同じデータベースに同じテーブルを作成した場合
※ 同じ名前のオブジェクトを作成する場合は、別データベースに作成しましょう。
- rootセッション
mysql> select user(),current_user(),database();
+-----------------+----------------+------------+
| user() | current_user() | database() |
+-----------------+----------------+------------+
| root@172.22.0.1 | root@% | POC |
+-----------------+----------------+------------+
1 row in set (0.00 sec)
mysql> create table t1 (c1 int,c2 text);
Query OK, 0 rows affected (0.06 sec)
- adminセッション
mysql> select user(),current_user(),database();
+------------------+----------------+------------+
| user() | current_user() | database() |
+------------------+----------------+------------+
| admin@172.22.0.1 | admin@% | POC |
+------------------+----------------+------------+
1 row in set (0.00 sec)
mysql> create table t1 (c1 int,c2 text);
ERROR 1050 (42S01): Table 't1' already exists
mysql>
確認②：異なったデータベースにテーブルを作成
mysql> select user(),current_user(),database();
+------------------+----------------+------------+
| user() | current_user() | database() |
+------------------+----------------+------------+
| admin@172.22.0.1 | admin@% | POC |
+------------------+----------------+------------+
1 row in set (0.00 sec)
mysql> create table t1 (c1 int,c2 text);
ERROR 1050 (42S01): Table 't1' already exists
mysql> create database POC2;
Query OK, 1 row affected (0.01 sec)
mysql> use POC2;
Database changed
mysql> create table t1 (c1 int,c2 text);
Query OK, 0 rows affected (0.04 sec)
mysql> select user(),current_user(),database();
+------------------+----------------+------------+
| user() | current_user() | database() |
+------------------+----------------+------------+
| admin@172.22.0.1 | admin@% | POC2 |
+------------------+----------------+------------+
1 row in set (0.00 sec)
