ソーシャルサービス系のデータベースを構築する～ ユーザー情報編

ユーザー情報テーブルを作成してみよう

今回、どんなサービス、およびシステムでもありそうなユーザーマスタテーブルを設計/構築してみたいと思います ただ、テーブルを作る、登録しただけですと、ちょっとつまらないので今回は、ダイナミックカラムと、PASSWORD関数を利用してみようと思います

キングジム パスワードマネージャー ミルパス PW10 ブラック

Kindle Paperwhite Wi-Fi、ブラック

・・とその前に、今後いくつか作られるであろう、テーブルの共通ルールを記載していきたいと思います

テーブル共通項目

id 列

どのテーブルにもかならず、id列 (*1) を作成する

created_at

レコードが作成された日時を設定する、ただしプログラムからは、設定しない（defaultで制御する）

updated_at

レコードが更新された日時を設定する、ただしプログラムからは設定しない（defualtで制御する） ※今回の設計では、MariaDB10.1を利用しますので、ちょっと特殊なカラム設定を利用します

hit.hateblo.jp

*1 IDリクワイアド アンチパターン IDリクワイアドは「すべてのテーブルに"id"という列名の無意味な連番の列を追加し、PRIMARY KEY制約を付与する」というパターンのこと。 の話がとある書籍から書いてあると思いますが、数百、場合によっては数千あるテーブルで、いちいち member_id だの、 customer_id だのと名前を変えられる場合を考えてみてください。データベース管理者の視点からみりゃ、地獄ですw

ここでは、すべてのテーブルにidというプライマリーキーを付与しますが、たとえば、userテーブルのidは、user_idだし、 customerテーブルのidは、customer_idです。暗黙的な意味づけで実装したほうが管理が楽です。

もちろん、id以外で一意性が必要な項目は、UNIQUE制約をつけます。UNIQUEをつけないで、id列以外の項目が重複して困ったという話がよくありますが、そもそも、それこそ設計不備だろｗと考えております

SQLアンチパターン

users テーブル生成

共通項目を踏襲した上で、以下のテーブルを作成しました

テーブル定義

 CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `login` varchar(200) NOT NULL,
  `passwd` varchar(200) NOT NULL,
  `mail` varchar(200) NOT NULL,
  `ext` blob,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix02_users` (`login`),
  KEY `ix01_users` (`mail`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ;

login

ログインIDを設定します。ここに本来ユーザーが記入するコードが入ってきます（user01とか、pandaとか）

passwd

パスワードが入ります。といっても、平文（クリアテキスト）で入るわけではありません、PASSWORD 関数でハッシュ化した文字が入ります。

• 例、passをhash化したもの

 SELECT PASSWORD('pass') ; 
+-------------------------------------------+
| PASSWORD('pass')                          |
+-------------------------------------------+
| *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
+-------------------------------------------+

※暗号化した文字は、複合化することはできません

mail

これは、そのまま、メールアドレスのエリアですね。

ext

拡張フィールドです、これは以前、説明したのような使い方で、たとえば性別とか、年齢を今回は入れております

user テーブルへINSERT

では、さっそくUSER情報をインサートしてみましょう

SET @login = 'user1' ;
SET @passwd = PASSWORD('user1pass') ;
SET @mail = 'hit@hateblo.jp' ;

SET @ext = COLUMN_CREATE(
  "age", 21,
  "sex", "man",
  "profile","ユーザー登録のテストです。よろしく"
) ;


INSERT INTO 
 users
(
  login,
  passwd,
  mail,
  ext
) VALUES (
  @login,
  @passwd,
  @mail,
  @ext
) ;

SET @login = 'user2' ;
SET @passwd = PASSWORD('user2pass') ;
SET @mail = 'user2@hateblo.jp' ;

SET @ext = COLUMN_CREATE(
  "age", 21,
  "sex", "man",
  "profile","ユーザー２になります。皆様よろしくお願いします。",
  "hobby","映画観賞"
) ;


INSERT INTO 
 users
(
  login,
  passwd,
  mail,
  ext
) VALUES (
  @login,
  @passwd,
  @mail,
  @ext
) ;

userテーブル登録結果確認

上記がレコード上、どのように登録されたか確認してみます

mysql>
SELECT id,login,passwd,mail,COLUMN_JSON(ext) FROM users \G                                                           
*************************** 1. row ***************************
              id: 1
           login: user1
          passwd: *F20B90D5A0CED3757C51AE04CD4700AB9879E467
            mail: hit@hateblo.jp
COLUMN_JSON(ext): {"age":21,"sex":"man","profile":"ユーザー登録のテストです。よろしく"}
*************************** 2. row ***************************
              id: 2
           login: user2
          passwd: *A9C95B38C9A88ECAE9128FD396059335E97CAA6E
            mail: user2@hateblo.jp
COLUMN_JSON(ext): {"age":21,"sex":"man","hobby":"映画観賞","profile":"ユーザー２になります。皆様よろしくお願いします。"}

意図したとおりに、データが登録されていることを確認できました。 extのカラムにつきましては、上記通り定義を変更せず、カラムの増減が行えますが、おそらくインデックス的には何も効かないので、あくまでも情報的なものだけを設定するほうがよいかと思います。 たとえば、今回の例ですと、ageという年齢カラムを設定しておりますが、今後、SQLで年齢が、30以上を検索が必要なサービスには向きません（汗　そういう場合は、ちゃんと、ageというカラムを作るべきだと思います

ユーザー情報を検索する

通常の検索はさておき、とりあえずログインするというのを前提にしたSQLはこんな感じになるのかな？と思います

SET @password='user1pass' ;
SET @login='user1' ;
SET @passwd=PASSWORD(@password) ;

SELECT
  id as user_id,
  login,
  COLUMN_GET(ext,'sex' AS CHAR) AS sex,
  COLUMN_GET(ext,'age' AS CHAR) AS age
FROM
  users
WHERE
  login = @login AND
  passwd = @passwd
;


+---------+-------+------+------+
| user_id | login | sex  | age  |
+---------+-------+------+------+
|       1 | user1 | man  | 21   |
+---------+-------+------+------+

ポイントとしては、一度平文で送られてきたパスワードをPASSWORD関数で、エンコードした結果を、WHERE条件に加えているところですね。こうしておけば、万が一、パスワードが流出してもわからなくなるはずです

今回はここまでー。