MySQL SQL文で自動インクリメントなどの機能を備えたusersテーブルを作る

目的

• idの自動インクリメントやNot NULL制約、データの不一致制約などの機能が盛り込まれたベーシックなusersテーブルの作成を行う

実施環境

• ハードウェア環境

項目	情報
OS	macOS Catalina(10.15.3)
PC機種	MacBook Pro (16-inch ,2019)
プロセッサ	2.6 GHz 6コアIntel Core i7
メモリ	16 GB 2667 MHz DDR4
グラフィックス	AMD Radeon Pro 5300M 4 GB Intel UHD Graphics 630 1536 MB

• ソフトウェア環境

項目	情報	備考
MySQLバージョン	8.0.19 for osx10.13 on x86_64	Homwbrewを用いて導入

前提環境

• 下記コマンドを実行してMySQLにログインすることができること。

  $ mysql -u root -p
  

  • command not foundと出力された方はこちら→Mac HomebrewでMySQLをインストールする
  • rootユーザのパスワードを失念してしまった方はこちら→MySQL 8.0.18 のrootパスワードを忘れた時のリセット方法

作成目標テーブルの確認

• testという名前のデータベースを作成し、その中にusersテーブルを作成する。

• 下記に作成するusersテーブルのイメージを記載する。

  id	name	email	create_at	update_at
  1	山田 太郎	[e-mail]	YYYY-MM-DD HH:MM:SS	YYYY-MM-DD HH:MM:SS

• usersテーブルの全てのカラムに共通する詳細情報を記載する。

  • NULLを許容しない。

• usersテーブルの各カラムの詳細情報を記載する。

  • idカラム: integer型おカラムとしてレコードが追加される際に自動インクリメントされた数値が格納される。このカラムを主キーとする。
  • nameカラム: varchar型のカラムとして文字数上限を30文字とする。
  • emailカラム: varchar型のカラムとして文字数上限を256文字とする。(Simple Mail Transfer Protocolでのアドレス文字数を根拠に決定)
  • create_atカラム: datetime型のカラムとして初回データ格納時に自動で日時を格納する。
  • update_atカラム: datatime型のカラムとして初回データ格納時にはcreate_atカラムと同じ値を格納し、当該レコードのデータに更新があった時の日時を格納する。

実施方法概要

1. ログイン
2. データベースの作成
3. テーブルの作成
4. 確認

実施方法詳細

1. ログイン

   1. 下記コマンドを実行してMySQLのターミナルを起動する。(MySQLのルートユーザのパスワードを忘れてしまった方はこちら→MySQL 8.0.18 のrootパスワードを忘れた時のリセット方法)

      $ mysql -u root -p
      

   2. MySQLにターミナルからログインできる事を確認する。(ターミナルにmysql>と表示されていればOK)

2. データベースの作成

   1. MySQLのにターミナルでログイン後、下記を実行してtestデータベースを作成する。

      mysql> create database test;
      

   2. 下記を実行してデータベースを指定する。

      mysql> use test;
      Database changed
      

3. テーブル作成

   1. 下記を実行してusersテーブルを作成する。

      mysql> create table users (
          -> id integer primary key auto_increment,
          -> name varchar(30) not null default 'user',
          -> email varchar(256) not null default '[e-mail]' unique,
          -> created_at datetime not null default current_timestamp,
          -> updated_at datetime not null default current_timestamp on update current_timestamp
          -> );
      

4. 確認

   1. 下記を実行して正常にテーブルが作成されている事を確認する。

      mysql> show columns from users;
      

   2. 下記のように出力される事を確認する。

      mysql> show columns from users;
      | Field      | Type         | Null | Key | Default           | Extra                                         |
      +------------+--------------+------+-----+-------------------+-----------------------------------------------+
      | id         | int          | NO   | PRI | NULL              | auto_increment                                |
      | name       | varchar(30)  | NO   |     | user              |                                               |
      | email      | varchar(256) | NO   | UNI | [e-mail] |                                               |
      | created_at | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
      | updated_at | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
      +------------+--------------+------+-----+-------------------+-----------------------------------------------+
      

   3. 下記を実行してダミーデータを格納する。

      mysql> insert into users
          -> (id, name)
          -> value (1, 'test_user');
      

   4. 下記を実行してダミーデータが正常に格納されている事を確認する。

      mysql> select *
          -> from users;
      

   5. 下記のように表示される事を確認する。

      mysql> select *
          -> from users;
      +----+-----------+-------------------+---------------------+---------------------+
      | id | name      | email             | created_at          | updated_at          |
      +----+-----------+-------------------+---------------------+---------------------+
      |  1 | test_user | [e-mail] | YYYY-MM-DD HH:MM:SS | YYYY-MM-DD HH:MM:SS |
      +----+-----------+-------------------+---------------------+---------------------+
      

   6. 下記を実行してダミーデータのnameを書き換える。

      mysql> update users
          -> set name = 'test_user_1'
          -> where id = 1;
      

   7. 下記を実行してダミーデータのnameが書き換わっている事を確認する。

      mysql> select *
          -> from users;
      

   8. 下記のように表示される事を確認する。(update_atも更新されている)

      mysql> select *
          -> from users;
      +----+-------------+-------------------+---------------------+---------------------+
      | id | name        | email             | created_at          | updated_at          |
      +----+-------------+-------------------+---------------------+---------------------+
      |  1 | test_user_1 | [e-mail] | YYYY-MM-DD HH:MM:SS | YYYY-MM-DD HH:MM:SS |
      +----+-------------+-------------------+---------------------+---------------------+