登陆系统的设计1 - 设计用户数据表

最近几天学习了设计登陆系统，首先需要设计一个数据表来储存用户数据。

mysql> describe user_info;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| user_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| username   | varchar(32) | NO   |     | NULL    |                |
| password   | varchar(40) | NO   |     | NULL    |                |
| join_date  | datetime    | YES  |     | NULL    |                |
| first_name | varchar(32) | YES  |     | NULL    |                |
| last_name  | varchar(32) | YES  |     | NULL    |                |
| gender     | varchar(1)  | YES  |     | NULL    |                |
| birthdate  | date        | YES  |     | NULL    |                |
| city       | varchar(32) | YES  |     | NULL    |                |
| state      | varchar(2)  | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

user_id为主键，username用户名，password密码。password的长度为40个字符，原因是用来储存SHA后的密码，即使数据库被窃取，也没用办法窃走用户的密码。

在添加新用户的时候只需要将密码用SHA()套起来就可以了。

mysql> INSERT INTO user_info(username, password, join_date) VALUES ('test', SHA('test'), NOW());

查询数据库得到以下结果：

mysql> SELECT * FROM user_info;
+---------+----------+------------------------------------------+---------------------+------------+-----------+--------+-----------+------+-------+
| user_id | username | password                                 | join_date           | first_name | last_name | gender | birthdate | city | state |
+---------+----------+------------------------------------------+---------------------+------------+-----------+--------+-----------+------+-------+
|       1 | test     | a94a8fe5ccb19ba61c4c0873d391e987982fbbd3 | 2013-09-12 16:32:28 | NULL       | NULL      | NULL   | NULL      | NULL | NULL  |
+---------+----------+------------------------------------------+---------------------+------------+-----------+--------+-----------+------+-------+
1 row in set (0.00 sec)