MySQLにおけるテーブル定義の確認方法
How to check the table definition in case of MySQL.
root@localhost [gis]> desc temp1;
+-----------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+----------------+
| town_id | bigint(20) | NO | PRI | NULL | auto_increment |
| pref_code | varchar(2) | NO | | - | |
| pref_name | varchar(4) | NO | | - | |
+-----------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
root@localhost [gis]> show create table temp1\G
*************************** 1. row ***************************
Table: temp1
Create Table: CREATE TABLE `temp1` (
`town_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '大字・町丁目レベルID',
`pref_code` varchar(2) NOT NULL DEFAULT '-' COMMENT 'JIS都道府県コード',
`pref_name` varchar(4) NOT NULL DEFAULT '-' COMMENT '都道府県名',
PRIMARY KEY (`town_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
root@localhost [gis]>
MySQLはTruncateと同時にauto_increment(unique identity)の値もリセット
root@localhost [gis]> select * from temp1;
+---------+-----------+-----------+
| town_id | pref_code | pref_name |
+---------+-----------+-----------+
| 1 | | |
| 3 | 1 | 北海道 |
| 4 | 2 | 青森 |
+---------+-----------+-----------+
3 rows in set (0.00 sec)
root@localhost [gis]> truncate table temp1;
Query OK, 0 rows affected (0.24 sec)
root@localhost [gis]> insert into temp1(pref_code,pref_name) values(1,'北海道');
Query OK, 1 row affected (0.04 sec)
root@localhost [gis]> insert into temp1(pref_code,pref_name) values(2,'青森');
Query OK, 1 row affected (0.00 sec)
root@localhost [gis]> select * from temp1;
+---------+-----------+-----------+
| town_id | pref_code | pref_name |
+---------+-----------+-----------+
| 1 | 1 | 北海道 |
| 2 | 2 | 青森 |
+---------+-----------+-----------+
2 rows in set (0.00 sec)
root@localhost [gis]> SELECT auto_increment FROM information_schema.tables WHERE table_schema = 'gis' and table_name = 'temp1'\G
*************************** 1. row ***************************
AUTO_INCREMENT: 101
1 row in set (0.01 sec)
root@localhost [gis]> show table status like 'temp1'\G
*************************** 1. row ***************************
Name: temp1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 101
Create_time: 2021-11-23 08:06:24
Update_time: 2021-11-23 08:06:45
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options: row_format=DYNAMIC
Comment:
1 row in set (0.00 sec)
root@localhost [gis]> PostgreSQLにおけるテーブル定義の確認方法
How to check the table definition in case of PostgreSQL
scaffold_app_development=# \d users;
Table "public.users"
Column | Type | Modifiers
------------+-----------------------------+----------------------------------------------------
id | bigint | not null default nextval('users_id_seq'::regclass)
name | character varying |
age | integer |
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
scaffold_app_development=# \d+ users;
Table "public.users"
Column | Type | Modifiers | Storage | Stats target | Description
------------+-----------------------------+----------------------------------------------------+----------+--------------+-------------
id | bigint | not null default nextval('users_id_seq'::regclass) | plain | |
name | character varying | | extended | |
age | integer | | plain | |
created_at | timestamp without time zone | not null | plain | |
updated_at | timestamp without time zone | not null | plain | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
Has OIDs: no
scaffold_app_development=#
scaffold_app_development=# \d users_id_seq ;
Sequence "public.users_id_seq"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | users_id_seq
last_value | bigint | 1
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 0
is_cycled | boolean | f
is_called | boolean | f
Owned by: public.users.id
\d: extra argument ";" ignored
scaffold_app_development=# ^
scaffold_app_development=# alter sequence users_id_seq restart with 1;
ALTER SEQUENCE
scaffold_app_development=# truncate table users;
TRUNCATE TABLE
scaffold_app_development=# insert into users(name,age,created_at,updated_at) values('MySQL','1',now(),now());
INSERT 0 1
scaffold_app_development=# insert into users(name,age,created_at,updated_at) values('PostgreSQL','2',now(),now());
INSERT 0 1
scaffold_app_development=# select * from users;
id | name | age | created_at | updated_at
----+------------+-----+----------------------------+----------------------------
1 | MySQL | 1 | 2021-11-23 07:35:05.535277 | 2021-11-23 07:35:05.535277
2 | PostgreSQL | 2 | 2021-11-23 07:35:09.025941 | 2021-11-23 07:35:09.025941
(2 rows)
scaffold_app_development=#
PostgreSQLではSequenceもTruncateと合わせてリセットする場合はrestart identityが必要
scaffold_app_development=# truncate table users;
TRUNCATE TABLE
scaffold_app_development=# select * from users;
id | name | age | created_at | updated_at
----+------+-----+------------+------------
(0 rows)
scaffold_app_development=# insert into users(name,age,created_at,updated_at) values('MySQL','1',now(),now());
INSERT 0 1
scaffold_app_development=# insert into users(name,age,created_at,updated_at) values('PostgreSQL','2',now(),now());
INSERT 0 1
scaffold_app_development=# select * from users;
id | name | age | created_at | updated_at
----+------------+-----+----------------------------+----------------------------
9 | MySQL | 1 | 2021-11-23 07:52:20.132661 | 2021-11-23 07:52:20.132661
10 | PostgreSQL | 2 | 2021-11-23 07:52:23.833254 | 2021-11-23 07:52:23.833254
(2 rows)
scaffold_app_development=# truncate table users restart identity;
TRUNCATE TABLE
scaffold_app_development=# insert into users(name,age,created_at,updated_at) values('MySQL','1',now(),now());
INSERT 0 1
scaffold_app_development=# insert into users(name,age,created_at,updated_at) values('PostgreSQL','2',now(),now());
INSERT 0 1
scaffold_app_development=# select * from users;
id | name | age | created_at | updated_at
----+------------+-----+----------------------------+----------------------------
1 | MySQL | 1 | 2021-11-23 07:52:55.786289 | 2021-11-23 07:52:55.786289
2 | PostgreSQL | 2 | 2021-11-23 07:52:59.041078 | 2021-11-23 07:52:59.041078
(2 rows)
scaffold_app_development=# SELECT * FROM information_schema.sequences where sequence_name = 'users_id_seq';
-[ RECORD 1 ]-----------+-------------------------
sequence_catalog | scaffold_app_development
sequence_schema | public
sequence_name | users_id_seq
data_type | bigint
numeric_precision | 64
numeric_precision_radix | 2
numeric_scale | 0
start_value | 1
minimum_value | 1
maximum_value | 9223372036854775807
increment | 1
cycle_option | NO
scaffold_app_development=# \d users_id_seq;
Sequence "public.users_id_seq"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | users_id_seq
last_value | bigint | 2
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 31
is_cycled | boolean | f
is_called | boolean | t
Owned by: public.users.id
scaffold_app_development=# 