【3】数据库的表设计和初始化

对于数据库的表设计，直接使用廖雪峰的教程当中的数据表

廖雪峰博客地址

我稍作更改摘抄过来：

主要更改：

id我个人比较喜欢用自增的int

创建时间数据类型改为datetime

用户表增加字段status，表示此账号是否可用

-- schema.sql

drop database if exists blog;

create database blog;

use blog;

grant all on blog.* to 'jakey'@'localhost' identified by '123';

create table users (
    `id` mediumint not null auto_increment,
    `email` varchar(50) not null,
    `password` varchar(50) not null,
    `admin` bool not null,
    `status` bool not null default 1,
    `name` varchar(50) not null,
    `image` varchar(500) not null,
    `created_at` datetime not null,
    unique key `idx_email` (`email`),
    key `idx_created_at` (`created_at`),
    primary key (`id`)
) engine=innodb default charset=utf8;

create table blogs (
    `id` mediumint not null auto_increment,
    `user_id` int not null,
    `user_name` varchar(50) not null,
    `user_image` varchar(500) not null,
    `name` varchar(50) not null,
    `summary` varchar(200) not null,
    `content` mediumtext not null,
    `created_at` datetime not null,
    key `idx_created_at` (`created_at`),
    primary key (`id`)
) engine=innodb default charset=utf8;

create table comments (
    `id` mediumint not null auto_increment,
    `blog_id` int not null,
    `user_id` int not null,
    `user_name` varchar(50) not null,
    `user_image` varchar(500) not null,
    `content` mediumtext not null,
    `created_at` datetime not null,
    key `idx_created_at` (`created_at`),
    primary key (`id`)
) engine=innodb default charset=utf8;

insert into users 
    (
        `email`,
        `password`,
        `admin`,
        `status`,
        `name`,
        `image`,
        `created_at`
    ) 
    values 
    (
        'Jakey.Chen@example.com',
        'MTIz',
        '1',
        '1',
        'Jakey.Chen',
        'None',
        now()
    );

 

主要有三个表：用户表，博文表，和评论表

在终端可进行初始化：

$ mysql -u root -p < schema.sql

确认是否初始化完成：

jakeychen@JakeyPC:~/Desktop$ mysql -u jakey -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.6.19-0ubuntu0.14.04.4 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use blog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_blog |
+----------------+
| blogs          |
| comments       |
| users          |
+----------------+
3 rows in set (0.00 sec)