和MySQL数据库关系表的设计不同 <这篇文章是网上已有的加以总结>
用户登录系统
记录用户登录信息表
- MySQL
1
mysql>
select
* from login;
2
+---------+----------------+-------------+---------------------+
3
| user_id | name | login_times | last_login_time |
4
+---------+----------------+-------------+---------------------+
5
| 1 | fk | 5 | 2013-09-26 00:00:00 |
6
| 2 | zhangsan | 1 | 2013-09-25 00:00:00 |
7
| 3 | zhaokuangyin | 2 | 2013-09-01 00:00:00 |
8
+---------+----------------+-------------+---------------------
其中:user_id->主键(可自增), login_times->每次登录自增, last_login_time->当前时间
- Redis
key <表名:主键名:列名> # 一般用冒号作为分隔符
value <列值>01
SET login:1:login_times 5
02
SET login:2:login_times 1
03
SET login:3:login_times 2
04
05
SET login:1:last_login_time 2013-09-26
06
SET login:2:last_login_time 2013-09-25
07
SET login:3:last_login_time 2013-09-01
08
09
SET login:1:name
"fk"
10
SET login:2:name
"zhangsan"
11
SET login:3:name
"zhaokuangyin"
1
SET
"login:fk:id"
1
2
SET
"login:zhangsan:id"
2
3
SET
"login:zhaokuangyin:id"
3
(1)用户登录操作如下:01
# -*- coding: utf-8 -*-
02
03
from
redis
import
client
04
import
time
05
06
r
=
client.Redis()
# 默认
07
# r = client.Redis(self, host='localhost', port=6379, db=0, password=None, socket_timeout=None, connection_pool=None, charset='utf-8', errors='strict', decode_responses=False, unix_socket_path=None)
08
# r = client.StrictRedis.from_url("redis://127.0.0.1:6381")
09
10
name
=
"fk"
11
12
# 根据name获取user_id
13
uid
=
r.get(
"login:%s:id"
%
name)
14
# 自增用户登录次数
15
ret
=
r.incr(
"login:%s:login_times"
%
uid)
16
# 更新最后登录时间
17
ret |
=
r.
set
(
"login:%s:last_login_time"
%
uid, time.strftime(
"%Y-%m-%d %H:%M:%S"
))
①MySQL1
select
*
from
login
order
by
last_login_time
desc
limit 10
②Redis01
"""查看最近登录用户(显示最近xx条评论用这个比较合适)
02
"""
03
N
=
10
04
# 把当前登录人添加到链表里(登录时就要操作)
05
ret
=
r.lpush(
"login:last_login_times"
, uid)
06
# 保持链表只有N位
07
ret |
=
r.ltrim(
"login:last_login_times"
,
0
, N
-
1
)
# ltrim (l表示list, trim 一个list,从索引 0到N-1)
08
09
# 获得最新登录人的id
10
last_login_list
=
r.lrange(
"login:last_login_times"
,
0
, N
-
1
)
01
"""获取登录次数最多用户
02
使用sorted set类型更适合
03
"""
04
# 把用户和登录次数统一存储在一个sorted set里
05
# ZADD key 权值 member,python中zadd(key, score, member)
06
r.zadd(
"login:login_times"
,
1
,
5
)
07
r.zadd(
"login:login_times"
,
2
,
1
)
08
r.zadd(
"login:login_times"
,
3
,
2
)
09
# 某个用户登录,需要额外维护一个sorted set
10
# 对该用户的登录次数自增1
11
# ret = r.zincrby("login:login_times", 1, uid) <-- 错误, 修正如下
12
ret
=
r.zincrby(
"login:login_times"
, uid,
1
)
# ***和redis官网命令的参数顺序是不一样的, zincrby(key, value, Δscore)
13
# 获取登录次数最多的用户, 逆序排列取的排名第N的用户即可
14
login_times_list
=
r.zrevrange(
"login:login_times"
,
0
, N
-
1
)
- ing
Tag系统
tag信息 存储设计
- MySQL
两张表,一张book的明细,一张tag表,表示每本的tag,一本书存在多个tag。01
mysql>
select
* from book;
02
+------+-------------------------------+----------------+
03
|
id
| name | author |
04
+------+-------------------------------+----------------+
05
| 1 | The Ruby Programming Language | Mark Pilgrim |
06
| 1 | Ruby on rail | David Flanagan |
07
| 1 | Programming Erlang | Joe Armstrong |
08
+------+-------------------------------+----------------+
09
10
mysql>
select
* from tag;
11
+---------+---------+
12
| tagname | book_id |
13
+---------+---------+
14
| ruby | 1 |
15
| ruby | 2 |
16
| web | 2 |
17
| erlang | 3 |
18
+---------+---------+
1
select
b.
name
, b.author
from
tag
as
t1, tag
as
t2, book
as
b
where
t1.tagname =
'web'
and
t2.tagname =
'ruby'
and
t1.book_id = t2.book_id
and
b.id = t1.book_id
点击打开链接 - Redis
book表用普通kv存储1
SET book:1:name
"The Ruby Programming Language"
2
SET book:2:name
"Ruby on rail"
3
SET book:3:name
"Programming Erlang"
4
5
SET book:1:author
"Mark Pilgrim"
6
SET book:2:author
"David Flanagan"
7
SET book:3:author
"Joe Armstrong"
1
SADD tag:ruby 1
2
SADD tag:ruby 2
3
SADD tag:web 2
4
SADD tag:erlang 3
1
# 既属于ruby,又属于web的书
2
ruby_and_web_list
=
r.sinter(
"tag:ruby"
,
"tag:web"
)
3
# 属于ruby,但不属于web的书
4
ruby_not_web_list
=
r.sdiff(
"tag:ruby"
,
"tag:web"
)
5
# 属于ruby, 或属于web的书
6
ruby_or_web_list
=
r.sunion(
"tag:ruby"
,
"