[MySQL] 【原创】REDIS与MYSQL实现标签的对比

CREATE TABLE `content` (
  `id` int(10) unsigned NOT NULL, -- 内容ID，唯一。
  `name` varchar(60) DEFAULT NULL, -- 内容的名字
  `created_timestamp` timestamp NULL DEFAULT NULL, -- 内容的加入时间
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `tag` (
  `tag_name` varchar(60) NOT NULL, -- 标签名字，唯一
  `visit_count` int(10) unsigned NOT NULL DEFAULT '0', -- 标签的访问次数
  PRIMARY KEY (`tag_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `content_tag_relation` (
  `content_id` int(10) unsigned NOT NULL, -- 内容ID
  `tag_name` varchar(60) NOT NULL -- 标签名字
) ENGINE=InnoDB DEFAULT CHARSET=latin1

假设我们有以下的需求：
1. 得到标签对应的文章名字，
SELECT a.name FROM content AS a,content_tag_relation AS b
WHERE a.id = b.content_id AND b.tag_name = 'mysql'
2. 按照访问量显示前三的标签，
SELECT tag_name FROM tag WHERE 1 ORDER BY visit_count DESC LIMIT 3;

t_girl:6379> set string:content_id:4  '{"name":"test48601","created_timestamp":"2012-01-01 05:41:01"}'
OK
t_girl:6379> get string:content_id:4
"{"name":"test48601","created_timestamp":"2012-01-01 05:41:01"}"
但是如果想得到内容对应的名字和创建时间，REDIS方面获取困难，就得交给程序来做了。

t_girl:6379> hset 'hset:content_id:4' name 'test48601'
(integer) 1
t_girl:6379> hset 'hset:content_id:4' created_timestamp '2012-01-01 05:41:01'
(integer) 1
那这时想获取对应的名字以及时间非常容易
t_girl:6379> hget hset:content_id:4 name
"test48601"
t_girl:6379> hget hset:content_id:4 created_timestamp
"2012-01-01 05:41:01"
t_girl:6379>

t_girl:6379> zadd zset:tag 680 database 469 db2
(integer) 2
比如我们想要得到访问前三的标签名字？
t_girl:6379> zrevrangebyscore zset:tag +inf 0 limit 0 3
1) "mongodb"
2) "sql"
3) "postgresql"

t_girl:6379> sadd set:content_id:4 role mongodb role database 
(integer) 3
那么也很容易得到指定内容对应的标签
t_girl:6379> smembers set:content_id:4
1) "database"
2) "role"
3) "mongodb"

import redis
  content_id_keys = r.keys('set*')
  content_id_keys_len = len(content_id_keys)
  i = 0
  j = 0
  content_name_list = []
  while i < content_id_keys_len:
    if r.sismember(content_id_keys[i],'mysql') == 1:
      content_name_list.append(eval(r.get(content_id_keys[i].replace('set','string')))['name'])
      print('Content name is :' + content_name_list[j])
      j += 1
    i += 1

t_girl:6379> sadd tag:mysql test123 test133 test144 test155
(integer) 4
t_girl:6379> smembers tag:mysql
1) "test133"
2) "test155"
3) "test123"
4) "test144"