我不建议使用传统的《数据字典》，我的做法是E-R图加数据库注释

注释伴随表，视图，触发器，过程等等，便于维护

用户帐号或通行证系统设计，下面以我的数库为例讲解。

我一般使用两个表 passport，profile 完成网站会员系统。

首先说说passport表，你也要以使用user或member等等命名，这个表设计尽可能地简单，不要使用过多字段。仅保存登录所必须用到的字段，如user,password,nickname,email... 登录帐号和密码做复合索引。

然后是profile表，这个表与passport是1:1关系，保存用户详细信息

这样设计可以保证海量用户登录时的速度。

		
+----------+
| user     |
|----------|
|id        | <---+
|username  |     |
|password  |     |
|nickname  |     |
|status    |     |
+----------+     |
                1:1
+----------+     |
| profile  |     |
|----------|     |
|user_id   | o---+
|name      |
|sex       |
|address   |
|telphone  |
|status    |
+----------+
		
		

			
CREATE TABLE `signup_keyloggers` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '唯一ID',
	`cookie` VARCHAR(32) NOT NULL COMMENT 'cookie id',
	`type` ENUM('baidu','google') NOT NULL COMMENT '推广账号类型',
	`field` ENUM('Name','Mobile','Email') NOT NULL COMMENT '字段名',
	`value` VARCHAR(50) NOT NULL COMMENT '值',
	`status` ENUM('New','Sent','Ignored','Called','Processed') NOT NULL DEFAULT 'New' COMMENT '状态',
	`operator` VARCHAR(10) NOT NULL COMMENT '操作人',
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`mtime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '状态修改时间',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `unique_index` (`type`, `cookie`, `field`, `value`)
)
COMMENT='用户注册键盘记录器'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
			
			

看具体情况，拆分表，可按“日”，“月”，“年”等等

		
      +-----------+
      | category  |
      |-----------|
  +-->|id         | <---+
  |   |title      |     |
  |   |description|    1:n
  |   |status     |     |
  |   |parent_id  | o---+
  |   +-----------+
  |
 1:n
  |
  |   +-----------------+            +------------------+
  |   | article_2008_01 |            | feedback_2008_01 |
  |   |-----------------|            |------------------|
  |   |id               |<--1:n--+   |id                |
  |   |title            |        |   |title             |
  |   |content          |        |   |content           |
  |   |datetime         |        |   |datetime          |
  |   |status           |        |   |status            |
  +--o|category_id      |        +--o|news_id           |
  +--o|user_id          |        +-->|user_id           |
  |   +-----------------+        |   +------------------+
  |                              |
 1:n  +----------+     +---1:n---+
  |   | user     |     |
  |   |----------|     |
  +-->|id        | <---+
      |user      |
      |passwd    |
      |nickname  |
      |status    |
      +----------+
		
		

		
      +-----------+
      | category  |
      |-----------|
  +-->|id         | <---+
  |   |title      |     |
  |   |description|    1:n
  |   |status     |     |
  |   |parent_id  | o---+
  |   +-----------+
  |
 1:n
  |
  |   +-----------------+            +-----------------+
  |   | article         |            | feedback        |
  |   |-----------------|            |-----------------|
  |   |id               |<--1:n--+   |id               |
  |   |title            |        |   |title            |
  |   |content          |        |   |content          |
  |   |datetime         |        |   |datetime         |
  |   |status           |        |   |status           |
  +--o|category_id      |        +--o|news_id          |
  +--o|user_id          |        +-->|user_id          |
  |   +-----------------+        |   +-----------------+
  |   | 2007,2008,2009  |        |   | 2007,2008,2009  |
  |   +-----------------+        |   +-----------------+
  |                              |
 1:n  +----------+     +---1:n---+
  |   | user     |     |
  |   |----------|     |
  +-->|id        | <---+
      |user      |
      |passwd    |
      |nickname  |
      |status    |
      +----------+
		
			

		
+----------+
| user     |
|----------|
|id        | <---+
|user      |     |
|passwd    |     |
|nickname  |     |
|status    |     |
+----------+     |
                1:n
+-----------+    |      +-----------+
| feedback  |    |      | news      |
|-----------|    |      |-----------|
|id         |    |  +-->|id         |
|title      |    |  |   |title      |
|content    |    |  |   |content    |
|datetime   |    | 1:n  |datetime   |
|status     |    |  |   |status     |
|user_id    |o---+  |   |user_id    |
|news_id    |o------+   +-----------+
+-----------+
		
		

		
+--------------+             +--------------+
| article      |             | article_rank |
|--------------|             |--------------|
|id            | <---1:1---o |article_id    |
|title         |             |click         |
|content       |             |read          |
|datetime      |             |score         |
|status        |             |...           |
|category_id   |             |...           |
|user_id       |             |...           |
+--------------+             +--------------+
		
		

		
+------------+              +----------------+               +------------------+
| product    |              | product_store  |               | user_order       |
+------------+              +----------------+               +------------------+
|id          | <--+         |id              | <---+         |id                |
|price       |    +--1:1--o |product_id      |     |         |user_id           |
|quantity    |              |sn              |     +--1:n--o |product_store_id  |
|...         |              |status          |               |                  |
|category_id |              +----------------+               +------------------+
+------------+
		
		

product 是产品表总表，product_store每个产品一条记录，同时将sn编号对应到物理产品，这时记录库存需要

			select count(id) from product_store where product_id='xxxxx' and status = 'sell'
		

商品销售

			begin;
			select id from product_store where status = 'sale' and product_id='xxxxx' for update;
			insert into user_order(user_id,product_store_id) values('xxxxxx','xxxxx');
			update product_store set status = 'sold' where status = 'sale' and product_id='xxxxx';
			commit;
		

售出的商品与用户的订单项一一对应的。

注意上面，这里使用了排它锁与事务处理，防止一个商品卖给两个人。

根据上面的思路我们可以将商品属性与product_store表进行一对一匹配，这样每个商品都有它自己的商品属性，甚至价格也可以移到product_store表中，例如不同颜色售价不同。

		
      +-----------+             +---------------+
      | category  |    .---+    | category_lang |
      |-----------|   /    |    +---------------+
  +-->|id         | <---+  +--o |category_id    |
  |   |title      |     |       |language_id    | o---+
  |   |description|    1:n      |name           |     |      +-------------+
  |   |status     |     |       +---------------+     .      | language    |
  |   |parent_id  | o---+                              \     +-------------+
  |   +-----------+                                     >--> |id           |
 1:n                                                   /     |lang         |
  |   +------------+                                  '      |status       |
  |   | product    |                                  |      +-------------+
  |   +------------+           +--------------+       |
  |   |id          | <---+     | product_lang |       |
  |   |price       |     |     +--------------+       |
  |   |quantity    |     +---o |product_id    |       |
  |   |...         |           |language_id   | o-----+
  +-o |category_id |           |name          |
      +------------+           +--------------+
		
		

		

   +------------+     +---------------+     +-----------+
   | user       |     | role_has_user |     | role      |
   +------------+     +---------------+     +-----------+
   |id          |o-+  |id             |  +->|id         |<-+
   |node_id     |  +->|user_id        |  |  |name       |  |
   |up_id       |     |role_id        |o-+  |description|  |
   +------------+	  +---------------+     +-----------+  |
		                                                   |
   +----------------+     +------------+                   |
   | workflow       |     | job        |                   |
   +----------------+     +------------+                   |
+->|id              |  +->|id          |                   |
|  |job_id          |o-+  |name        |                   |
+-o|up_id           |     |role_id     |o------------------+
   |                |     |description |
   +----------------+	  +------------+

		
		

主表

			CREATE TABLE `article` (
			`article_id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
			`cat_id` SMALLINT(5) NOT NULL DEFAULT '0',
			`title` VARCHAR(150) NOT NULL DEFAULT '',
			`content` LONGTEXT NOT NULL,
			`author` VARCHAR(30) NOT NULL DEFAULT '',
			`keywords` VARCHAR(255) NOT NULL DEFAULT '',
			PRIMARY KEY (`article_id`),
			INDEX `cat_id` (`cat_id`)
			)
			ENGINE=MyISAM
			ROW_FORMAT=DEFAULT
			AUTO_INCREMENT=1
		

本版控制表，用于记录每次变动

			CREATE TABLE `article_history` (
			`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
			`article_id` MEDIUMINT(8) UNSIGNED NOT NULL,
			`cat_id` SMALLINT(5) NOT NULL DEFAULT '0',
			`title` VARCHAR(150) NOT NULL DEFAULT '',
			`content` LONGTEXT NOT NULL,
			`author` VARCHAR(30) NOT NULL DEFAULT '',
			`keywords` VARCHAR(255) NOT NULL DEFAULT '',
			PRIMARY KEY (`id`),
			INDEX `article_id` (`article_id`)
			)
			ENGINE=MyISAM
			ROW_FORMAT=DEFAULT
			AUTO_INCREMENT=1
		

版本控制触发器

			DROP TRIGGER article_history;

			DELIMITER //
			CREATE TRIGGER article_history BEFORE update ON article FOR EACH ROW
			BEGIN
			INSERT INTO article_history SELECT * FROM article WHERE article_id = OLD.article_id;
			END; //
			DELIMITER;
		

			CREATE TABLE `logging` (
			`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
			`tag` ENUM('unknow','www','user','admin') NOT NULL DEFAULT 'unknow' COMMENT '日志标签',
			`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '产生时间',
			`facility` ENUM('bank','unionpay','sms','email') NOT NULL COMMENT '类别',
			`priority` ENUM('info','warning','error','critical','exception','debug') NOT NULL COMMENT '级别',
			`message` VARCHAR(512) NOT NULL COMMENT '内容',
			PRIMARY KEY (`id`)
			)
			COMMENT='日志表'
			COLLATE='utf8_general_ci'
			ENGINE=InnoDB
			AUTO_INCREMENT=2;
		

分区日志表

		
delimiter $$

CREATE TABLE `logging` (
  `tag` enum('unknow','login','info','admin','cron','manual') NOT NULL DEFAULT 'unknow' COMMENT '日志标签',
  `asctime` datetime NOT NULL COMMENT '产生时间',
  `facility` enum('account','bank','unionpay','sms','email','unknow') NOT NULL DEFAULT 'unknow' COMMENT '类别',
  `priority` enum('info','warning','error','critical','exception','debug') NOT NULL DEFAULT 'debug' COMMENT '级别',
  `message` varchar(512) NOT NULL COMMENT '内容',
  `operator` varchar(50) NOT NULL DEFAULT 'computer' COMMENT '操作者'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (YEAR(asctime))
SUBPARTITION BY HASH (MONTH(asctime))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */$$
		
		

分表+分区，每年分表一次，每个分区中保存一个月的数据

			delimiter $$

			CREATE TABLE `logging_2013` (
			`tag` enum('unknow','login','info','admin','cron','manual') NOT NULL DEFAULT 'unknow' COMMENT '日志标签',
			`asctime` datetime NOT NULL COMMENT '产生时间',
			`facility` enum('account','bank','unionpay','sms','email','unknow') NOT NULL DEFAULT 'unknow' COMMENT '类别',
			`priority` enum('info','warning','error','critical','exception','debug') NOT NULL DEFAULT 'debug' COMMENT '级别',
			`message` varchar(512) NOT NULL COMMENT '内容',
			`operator` varchar(50) NOT NULL DEFAULT 'computer' COMMENT '操作者'
			) ENGINE=InnoDB DEFAULT CHARSET=utf8
			/*!50100 PARTITION BY LIST (MONTH(asctime))
			SUBPARTITION BY KEY (facility)
			(PARTITION part0 VALUES IN (1) ENGINE = InnoDB,
			PARTITION part1 VALUES IN (2) ENGINE = InnoDB,
			PARTITION part2 VALUES IN (3) ENGINE = InnoDB,
			PARTITION part3 VALUES IN (4) ENGINE = InnoDB,
			PARTITION part4 VALUES IN (5) ENGINE = InnoDB,
			PARTITION part5 VALUES IN (6) ENGINE = InnoDB,
			PARTITION part6 VALUES IN (7) ENGINE = InnoDB,
			PARTITION part7 VALUES IN (8) ENGINE = InnoDB,
			PARTITION part8 VALUES IN (9) ENGINE = InnoDB,
			PARTITION part9 VALUES IN (10) ENGINE = InnoDB,
			PARTITION part10 VALUES IN (11) ENGINE = InnoDB,
			PARTITION part11 VALUES IN (12) ENGINE = InnoDB) */$$
		

命名分区

			delimiter $$

			CREATE TABLE `logging_2012` (
			`tag` enum('unknow','login','info','admin','cron','manual') NOT NULL DEFAULT 'unknow' COMMENT '日志标签',
			`asctime` datetime NOT NULL COMMENT '产生时间',
			`facility` enum('account','bank','unionpay','sms','email','unknow') NOT NULL DEFAULT 'unknow' COMMENT '类别',
			`priority` enum('info','warning','error','critical','exception','debug') NOT NULL DEFAULT 'debug' COMMENT '级别',
			`message` varchar(512) NOT NULL COMMENT '内容',
			`operator` varchar(50) NOT NULL DEFAULT 'computer' COMMENT '操作者'
			) ENGINE=InnoDB DEFAULT CHARSET=utf8
			/*!50100 PARTITION BY LIST (MONTH(asctime))
			SUBPARTITION BY KEY (facility)
			(PARTITION January VALUES IN (1) ENGINE = InnoDB,
			PARTITION February VALUES IN (2) ENGINE = InnoDB,
			PARTITION March VALUES IN (3) ENGINE = InnoDB,
			PARTITION April VALUES IN (4) ENGINE = InnoDB,
			PARTITION May VALUES IN (5) ENGINE = InnoDB,
			PARTITION June VALUES IN (6) ENGINE = InnoDB,
			PARTITION July VALUES IN (7) ENGINE = InnoDB,
			PARTITION August VALUES IN (8) ENGINE = InnoDB,
			PARTITION September VALUES IN (9) ENGINE = InnoDB,
			PARTITION October VALUES IN (10) ENGINE = InnoDB,
			PARTITION November VALUES IN (11) ENGINE = InnoDB,
			PARTITION December VALUES IN (12) ENGINE = InnoDB) */$$
		

		
DROP TABLE IF EXISTS `uuid_test`;
CREATE TABLE IF NOT EXISTS `uuid_test` (
  `uuid` varchar(36) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='uuid 测试';
		
		

插入触发器

		
DROP TRIGGER IF EXISTS `uuid_test_insert`;
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `uuid_test_insert` BEFORE INSERT ON `uuid_test` FOR EACH ROW BEGIN
	IF new.uuid is null or new.uuid = '' or length(new.uuid) != 36 THEN
		set new.uuid=uuid();
	END IF;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;
		
		

ID放撰改触发器

		
DROP TRIGGER IF EXISTS `uuid_test_update`;
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `uuid_test_update` BEFORE UPDATE ON `uuid_test` FOR EACH ROW BEGIN
	set new.uuid = old.uuid;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;
		
		

很多时候我们需要使用数据库存储配置项，由于各种原因我们无法使用配置文件来完成，例如在一个有很多节点集群环境中使用文件配置文件时非常不方便。

		
DROP TABLE IF EXISTS `config`;
CREATE TABLE IF NOT EXISTS `config` (
  `key` varchar(50) NOT NULL,
  `value` varchar(50) NOT NULL,
  `operator` varchar(50) NOT NULL DEFAULT 'dba',
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站动态配置文件';


INSERT INTO `config` (`key`, `value`, `operator`, `mtime`) VALUES
	('cache.apc', 'ON', 'dba', '2013-07-18 16:17:13'),
	('cache.file.path', '/tmp', 'dba', '2013-07-18 16:17:57'),
	('cache.redis', 'YES', 'dba', '2013-07-18 16:17:22'),
	('payment.alipay.status', 'Enabled', 'dba', '2013-07-18 16:15:15'),
	('payment.alipay.url', 'http://xx.comx.com', 'dba', '2013-07-18 16:16:38'),
	('payment.yeepay.status', 'Enabled', 'dba', '2013-07-18 16:15:17'),
	('payment.99bill.status', 'Enabled', 'dba', '2013-07-18 16:15:10'),
	('payment.zqpay.status', 'Disabled', 'dba', '2013-07-18 16:15:20');
		
		

配置项key的写法很讲究

			单个配置
			database.host=localhost
			database.user=user
			database.pass=pass

			多个配置
			database.1.host=localhost
			database.1.user=user
			database.1.pass=pass
			database.1.status=1

			database.2.host=localhost
			database.2.user=user
			database.2.pass=pass
			database.2.status=1

			优化配置项，例如：payment.alipay.status 可以这样优化
			payment.status.alipay
			payment.status.yeepay
		

这样做的目的是为了更好的使用like进行查询

			select `key`,`value` from config where `key` like 'payment.status.%';
			select `key`,`value` from config where `key` like 'database.?.status';
		

				CREATE TABLE `config_fee` (
				`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
				`level` INT(11) NULL DEFAULT NULL COMMENT '层级',
				`type` ENUM('Deposit','Withdrawing') NOT NULL DEFAULT 'Withdrawing' COMMENT '类型，存款，取款',
				`min_fee` FLOAT(10,2) NOT NULL COMMENT '最低手续费',
				`max_fee` FLOAT(10,2) NOT NULL COMMENT '最高手续费',
				`ratio` FLOAT(10,2) NOT NULL COMMENT '手续费比例',
				`operator` VARCHAR(10) NOT NULL COMMENT '操作者',
				`status` ENUM('Current','Trash') NOT NULL DEFAULT 'Current',
				`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
				`mtime` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
				PRIMARY KEY (`id`)
				)
				COMMENT='手续费管理'
				COLLATE='utf8_general_ci'
				ENGINE=InnoDB;
			

			
mysql> select type,operator,status,ctime,mtime from config_mtf_fee;
+---------+----------+---------+---------------------+---------------------+
| type    | operator | status  | ctime               | mtime               |
+---------+----------+---------+---------------------+---------------------+
| Deposit | 141      | Trash   | 2014-08-28 11:10:17 | 2014-08-28 11:10:57 |
| Deposit | 141      | Trash   | 2014-08-28 11:10:17 | 2014-08-28 11:10:57 |
| Deposit | 141      | Trash   | 2014-08-28 11:10:17 | 2014-08-28 11:10:57 |
| Deposit | 141      | Trash   | 2014-08-28 11:10:17 | 2014-08-28 11:10:57 |
| Deposit | 324      | Current | 2014-08-28 11:10:54 | 2014-08-28 11:10:59 |
+---------+----------+---------+---------------------+---------------------+
2 rows in set (0.00 sec)
			
			

用户注册，登陆等等需要验证码，下面的方案是，请求验证码生成一个随机验证码，存在code中，identity可以存储来源IP/手机号码/Cookie等等用户校验， 5分钟内如果没有被使用就会删除，如果5分钟内被使用也会删除。type 可以存放www,user,bbs,admin.....等等，将所有验证码放在captcha表中统一管理。

这个方案主要是考虑没有memcache/redis/apc/xcache等等缓存环境下的解决方案，你也可以将下表改造一下，增加ttl字段用于存放生存时间，而不是采用5分钟一刀切的方案。

			CREATE TABLE `captcha` (
			`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
			`type` ENUM('user','admin') NOT NULL DEFAULT 'admin' COMMENT '验证码类型',
			`identity` VARCHAR(32) NOT NULL COMMENT '唯一身份识别md5摘要',
			`code` VARCHAR(6) NOT NULL COMMENT '验证码',
			`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
			PRIMARY KEY (`id`),
			UNIQUE INDEX `code` (`code`),
			UNIQUE INDEX `identity` (`identity`)
			)
			COMMENT='验证码'
			COLLATE='utf8_general_ci'
			ENGINE=InnoDB;
		

		
CREATE EVENT `captcha` ON SCHEDULE
		EVERY 5 MINUTE STARTS '2013-07-08 16:27:03'
	ON COMPLETION PRESERVE
	ENABLE
	COMMENT ''
	DO BEGIN
	delete from captcha where type='myid' and ctime < DATE_ADD(now(), INTERVAL -5 MINUTE);
END
		
		

members_location 表与 members 表是一对一关系，该表只负责存储归属地信息

			DROP TABLE IF EXISTS `members_location`;
			CREATE TABLE IF NOT EXISTS `members_location` (
			`id` int(10) unsigned NOT NULL,
			`province` varchar(50) NOT NULL,
			`city` varchar(50) NOT NULL,
			`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (`id`),
			KEY `province` (`province`),
			KEY `city` (`city`),
			CONSTRAINT `FK_members_location_members` FOREIGN KEY (`id`) REFERENCES `members` (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		

当某些用户符合条件需要查询归属地是，只要将其插入到 members_mobile 表即可。该表使用黑洞引擎并不会存储手机号码，所以明文手机号码安全得到了保障。

			DROP TABLE IF EXISTS `members_mobile`;
			CREATE TABLE IF NOT EXISTS `members_mobile` (
			`id` int(10) NOT NULL,
			`number` varchar(11) NOT NULL
			) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8;
		

当有数据进入到 members_mobile 时出发器 members_mobile_insert 会工作，去 mobile_location 表中查询归属地后保存在 members_location 表中

			DROP TRIGGER IF EXISTS `members_mobile_insert`;
			SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
			DELIMITER //
			CREATE TRIGGER `members_mobile_insert` BEFORE INSERT ON `members_mobile` FOR EACH ROW BEGIN
			insert into members_location(id,province,city) select NEW.id,mobile_location.province,mobile_location.city from mobile_location where mobile_location.id = md5(LEFT(NEW.number, 7));
			END//
			DELIMITER ;
			SET SQL_MODE=@OLDTMP_SQL_MODE;
		

mobile_location 是存储手机号段与归属地信息的数据库

			DROP TABLE IF EXISTS `mobile_location`;
			CREATE TABLE IF NOT EXISTS `mobile_location` (
			`id` varchar(50) NOT NULL,
			`province` varchar(50) DEFAULT NULL,
			`city` varchar(50) DEFAULT NULL,
			PRIMARY KEY (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		

ctime 为创建时间, mtime 是修改时间默认CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

			CREATE TABLE `trades_platform` (
			`id` INT(10) UNSIGNED NULL DEFAULT NULL,
			`type` ENUM('A','B','C') NOT NULL,
			`login` VARCHAR(10) NOT NULL,
			`password` VARCHAR(10) NOT NULL,
			`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
			`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
			)
			COMMENT='平台'
			ENGINE=InnoDB;
		

我们还可以让mtime默认为NULL,这样更节省存储空间。

			`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
			`mtime` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
		

该表的功能是显示在线用户，控制多点登陆，防止异常退出

		
CREATE TABLE IF NOT EXISTS `employees_online` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL COMMENT 'User ID',
  `ipaddr` varchar(15) NOT NULL COMMENT 'IP Address',
  `expire` datetime NOT NULL COMMENT 'Expire time',
  `status` enum('Login','Logout','Offline') NOT NULL DEFAULT 'Login' COMMENT 'Current Status',
  `message` varchar(255) NOT NULL COMMENT 'Leave a message',
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created Time',
  `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modified Time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='show listing of last logged in users';

DELIMITER //
CREATE DEFINER=`dba`@`192.168.%` EVENT `employees_online` ON SCHEDULE EVERY 15 MINUTE STARTS '2014-08-22 10:33:24' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Employees Online Logging' DO BEGIN
	update employees_online set `status` = 'Offline' where expire < now() and ctime > DATE_ADD(now(), INTERVAL -15 MINUTE);
END//
DELIMITER ;

SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `employees_online_before_delete` BEFORE DELETE ON `employees_online` FOR EACH ROW BEGIN
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Permission denied', MYSQL_ERRNO = 1001;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `employees_online_before_update` BEFORE UPDATE ON `employees_online` FOR EACH ROW BEGIN
	SET NEW.`id` = OLD.id;
	SET NEW.`username` = OLD.username;
	SET NEW.`ipaddr` = OLD.ipaddr;
	SET NEW.`message` = OLD.message;
	SET NEW.`ctime` = OLD.ctime;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;	
		
		

登陆日志将永久保存，防止数据被删除由触发器employees_online_before_delete负责

防止有人撰改登陆信息，由触发器employees_online_before_update负责，主要是防止撰改登陆名与IP地址，这样讲不能从其他电脑登陆，必须用户Logout才能在其他电脑登陆。

实现 PHP strip_tags 函数的功能。

		
CREATE DEFINER=`dba`@`%` FUNCTION `strip_tags`(`$str` TEXT)
RETURNS text CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE $start, $end INT DEFAULT 1;
    LOOP
        SET $start = LOCATE("<", $str, $start);
        IF (!$start) THEN RETURN $str; END IF;
        SET $end = LOCATE(">", $str, $start);
        IF (!$end) THEN SET $end = $start; END IF;
        SET $str = INSERT($str, $start, $end - $start + 1, "");
    END LOOP;
END		
		
		

		
mysql> select strip_tags('<span><i>hello</i> <b>world</b>!!! <br /><a href="//www.netkiller/">netkiller</a>');
+----------------------------------------------------------------------+
| strip_tags('<span>hel<b>lo <a href="world">wo<>rld</a> <<x>again<.') |
+----------------------------------------------------------------------+
| hello world again.                                                   |
+----------------------------------------------------------------------+
1 row in set

mysql> select strip_tags('<span style="color:red"><i>hello</i> <b id="world" >world</b>!!! <br /><a class="home" href="//www.netkiller/">netkiller</a><span>') as TEXT;
+--------------------------+
| TEXT                     |
+--------------------------+
| hello world!!! netkiller |
+--------------------------+
1 row in set (0.00 sec)
		
		

这里讲解SNS交友社区的数据库设计与实现

我们要实现下面几个功能

		
CREATE DATABASE `sns` /*!40100 COLLATE 'utf8_general_ci' */
		
		

			
CREATE TABLE `people` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
)
COMMENT='Social Network Site - Six Degrees of Separation - http://www.netkiller.cn'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
			
			

			
CREATE TABLE `friend` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`people_id` INT(10) UNSIGNED NOT NULL,
	`friend_id` INT(10) UNSIGNED NOT NULL,
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `unique` (`people_id`, `friend_id`),
	INDEX `FK_firend_people` (`people_id`),
	INDEX `FK_firend_people_2` (`friend_id`),
	CONSTRAINT `FK_firend_people` FOREIGN KEY (`people_id`) REFERENCES `people` (`id`),
	CONSTRAINT `FK_firend_people_2` FOREIGN KEY (`friend_id`) REFERENCES `people` (`id`)
)
COMMENT='Social Network Site - Six Degrees of Separation - http://www.netkiller.cn'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

			
			

			
INSERT INTO `people` (`id`, `name`) VALUES
	(1, 'Neo'),
	(2, 'Luke'),
	(3, 'Jack'),
	(4, 'Joey'),
	(5, 'Jam'),
	(6, 'John');
			
			

			
INSERT INTO `friend` (`id`, `people_id`, `friend_id`) VALUES
	(1, 1, 2),
	(2, 1, 3),
	(3, 1, 4),
	(4, 1, 5),
	(5, 1, 6),
	(6, 2, 1),
	(7, 2, 3);
			
			

			
select people.* from friend, people where friend.people_id = 1 and friend.friend_id = people.id;
			
			

			
CREATE TABLE `network` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`people_id` INT(10) UNSIGNED NOT NULL,
	`following_id` INT(10) UNSIGNED NOT NULL,
	`friend_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`degrees` VARCHAR(250) NOT NULL,
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `unique` (`people_id`, `friend_id`, `following_id`),
	INDEX `FK_firend_people` (`people_id`),
	INDEX `FK_firend_people_2` (`friend_id`),
	INDEX `FK_friend_people_following_id` (`following_id`),
	CONSTRAINT `FK_firend_people` FOREIGN KEY (`people_id`) REFERENCES `people` (`id`),
	CONSTRAINT `FK_friend_people_following_id` FOREIGN KEY (`following_id`) REFERENCES `people` (`id`),
	CONSTRAINT `FK_friend_people_friend_id` FOREIGN KEY (`friend_id`) REFERENCES `people` (`id`)
)
COMMENT='Social Network Site - Six Degrees of Separation - http://www.netkiller.cn'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

			
			

			
INSERT INTO `people` (`id`, `name`) VALUES
	(1, 'Neo'),
	(2, 'Luke'),
	(3, 'Jack'),
	(4, 'Joey'),
	(5, 'Jam'),
	(6, 'John');

INSERT INTO `network` (`people_id`, `following_id`, `friend_id`, `degrees`) VALUES ( 1, 5, NULL, '1.5');
			
			

			
select * from network where people_id=1 and friend_id = 5;

INSERT INTO `sns`.`network` (`people_id`, `following_id`, `friend_id`, `degrees`) VALUES ('1', '6', '5', '1.5.6');
			
			

			
INSERT INTO `sns`.`network` (`people_id`, `following_id`, `friend_id`, `degrees`) VALUES ('1', '4', '2', '1.2.4');
			
			

			
select * from network where people_id=1 and degrees like "1.%";
select * from network where people_id=1 and degrees like "1.2%";
select * from network where people_id=1 and degrees like "1.2.%";
			
			

使用枚举: