学步园

--
-- Tables
--

DROP DATABASE IF EXISTS `guild`;
CREATE DATABASE `guild`;

CREATE TABLE `guild`.`info` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
	`name` varchar(20) NOT NULL,
	`owner` BIGINT UNSIGNED NOT NULL ,
	`sid` TINYINT UNSIGNED NOT NULL , -- type and template id
	`desc` varchar(100) NOT NULL,
	`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,-- create time
	PRIMARY KEY (`id`),
	INDEX `owner_INDEX` (`owner`) 
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `guild`.`member` (
	`gid` BIGINT UNSIGNED NOT NULL ,
	`uid` BIGINT UNSIGNED NOT NULL ,
	`group` TINYINT UNSIGNED NOT NULL DEFAULT 0, -- type of guild member
	`joinTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,-- create time
	UNIQUE KEY `guild_user_INDEX` (`gid`, `uid`) 
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `guild`.`chat` (
	`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
	`gid` BIGINT UNSIGNED NOT NULL ,
	`uid` BIGINT UNSIGNED NOT NULL ,
	`type` TINYINT UNSIGNED NOT NULL, -- system or user
	`content` varchar(100) NOT NULL,
	`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,-- create time
	PRIMARY KEY (`id`) ,
	INDEX `guild_INDEX` (`gid`) 
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

以前只会简单的create table， 对于索引、engine等都没有设置过。

做个备忘。

primary 会有个索引。 index会增加新的索引字段。