Redis键-值数据库 nosql 数据建模（2）------ 如何存储主从表数据 一对少关系
程序员文章站
2022-07-12 20:57:39
...
作者：QQ 14588019 WonderfulLife
一对少关系（主从关系中，明细在10条记录以内）
users表 (主表)userid username birth
1 Jack 1986-12-06
2 Jason 1980-08-01
3 Jim 1977-01-25
contacts表 (从表)
contactid userid mobile email
1 1 13570845544 aaa@qq.com
2 1 13800138000 aaa@qq.com
3 1 13713142092 aaa@qq.com
4 2 13800138120 aaa@qq.com
5 2 13800138119 aaa@qq.com
6 3 13000138120 aaa@qq.com
7 3 13500138119 aaa@qq.com
8 3 13600138120 aaa@qq.com
9 3 13900138119 aaa@qq.com
如果是上述主从明细数量关系 ------ 明细记录只有几条记录，我们就不要建立主从表啦，
用一个users表内嵌从表记录才是是正确的关系型数据库建模方式，那么问题就转换成单表操作啦：
users表 其中contacts字段的数据类型为json类型，它实际上是个字符串：
userid username birth contacts
1 Jack 1986-12-06
2 Jason 1980-08-01
3 Jim 1977-01-25
因为排版太丑的原因以上记录contacts字段没有填写它们的字段内容，下面按顺序给出它们的json字符串值：
[{\"mobile\":\"13570845544\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13800138000\",\"email\":\"aaa@qq.com\"}]
[{\"mobile\":\"13800138120\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13800138119\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13800138119\",\"email\":\"aaa@qq.com\"}]
[{\"mobile\":\"13000138120\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13500138119\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13600138120\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13900138119\",\"email\":\"aaa@qq.com\"}]
既然主从表变成了单表建模，那么我们就按照单表建模用Redis实现的多种nosql建模套路仿照实现吧，
不过我还是随便挑一种单表建模的套路写个完整的范例：
第1组指令添加以下这条记录：
userid username birth contacts
1 Jack 1986-12-06 [{\"mobile\":\"13570845544\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13800138000\",\"email\":\"aaa@qq.com\"}]
MULTI
SET user:1:userid 1
SET user:1:username Jack
SET user:1:birth "1986-12-06"
SET user:1:contacts "[{\"mobile\":\"13570845544\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13800138000\",\"email\":\"aaa@qq.com\"}]"
LPUSH user:list user:1:userid
EXEC
第2组指令添加以下这条记录：
userid username birth contacts
2 Jason 1980-08-01 [{\"mobile\":\"13800138120\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13800138119\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13800138119\",\"email\":\"aaa@qq.com\"}]
MULTI
SET user:2:userid 2
SET user:2:username Jason
SET user:2:birth "1980-08-01"
SET user:2:contacts "[{\"mobile\":\"13800138120\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13800138119\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13800138119\",\"email\":\"aaa@qq.com\"}]"
LPUSH user:list user:2:userid
EXEC
第3组指令添加以下这条记录：
userid username birth contacts
3 Jim 1977-01-25 [{\"mobile\":\"13000138120\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13500138119\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13600138120\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13900138119\",\"email\":\"aaa@qq.com\"}]
MULTI
MSET user:3:userid 3 user:3:username "Jim" user:3:birth "1977-01-25" user:3:contacts "[{\"mobile\":\"13000138120\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13500138119\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13600138120\",\"email\":\"aaa@qq.com\"},{\"mobile\":\"13900138119\",\"email\":\"aaa@qq.com\"}]"
LPUSH user:list user:3:userid
EXEC
如果使用user:list队列（List类型的数据）存储记录的主键字符串来进行分页：
127.0.0.1:6379> LLEN user:list # 返回记录总数
127.0.0.1:6379> LRANGE user:list 0 20 # 这是第1页的20条记录主键
127.0.0.1:6379> LRANGE user:list 21 40 # 这是第2页的20条记录主键
127.0.0.1:6379> LRANGE user:list 41 60 # 这是第3页的20条记录主键<?php
namespace app\index\controller;
use think\Controller;
use think\Request;
use think\Db;
class User extends Controller {
public function index() {
$redis = new \Redis();
$redis->connect('127.0.0.1', 6379);
$redis->flushAll();
// 添加第1条记录
$json = json_encode([
['mobile' => '13570845544', 'email' => 'aaa@qq.com'],
['mobile' => '13800138000', 'email' => 'aaa@qq.com'],
['mobile' => '13713142092', 'email' => 'aaa@qq.com']
]);
$id = 1;
$redis->multi();
$redis->set("user:$id:userid", $id);
$redis->set("user:$id:username", "Jack");
$redis->set("user:$id:birth", "1986-12-06");
$redis->set("user:$id:contacts", $json);
$redis->lPush("user:list", $id);
$redis->exec();
// 添加第2条记录
$json = json_encode([
['mobile' => '13800138120', 'email' => 'aaa@qq.com'],
['mobile' => '13800138119', 'email' => 'aaa@qq.com']
]);
$id = 2;
$redis->multi();
$redis->set("user:$id:userid", $id);
$redis->set("user:$id:username", "Jason");
$redis->set("user:$id:birth", "1980-08-01");
$redis->set("user:$id:contacts", $json);
$redis->lPush("user:list", $id);
$redis->exec();
// 添加第3条记录
$json = json_encode([
['mobile' => '13000138120', 'email' => 'aaa@qq.com'],
['mobile' => '13500138119', 'email' => 'aaa@qq.com'],
['mobile' => '13600138120', 'email' => 'aaa@qq.com'],
['mobile' => '13900138119', 'email' => 'aaa@qq.com']
]);
$id = 3;
$redis->multi();
$redis->mset([
"user:$id:userid" => $id,
"user:$id:username" => "Jim",
"user:$id:birth" => "1977-01-25",
"user:$id:contacts" => $json
]);
$redis->lPush("user:list", $id);
$redis->exec();
// 分页
$users_count = $redis->lSize("user:list");
$users_pagers = $users_count % 20 > 0 ? $users_count / 20 + 1 : $users_count / 20;
$current_page = 1;
$users = [];
if ($current_page < $users_pagers + 1) {
$user_list_id = $redis->lrange("user:list", ($current_page - 1) * 20, $current_page * 20);
foreach ($user_list_id as $id) {
$userid = $redis->get("user:$id:userid");
$username = $redis->get("user:$id:username");
$birth = $redis->get("user:$id:birth");
$contacts = $redis->get("user:$id:contacts");
$users[] = [
'userid' => $userid,
'username' => $username,
'birth' => $birth,
'contacts' => $contacts
];
}
}
return json_encode($users);
/*
// 直接使用 key-json字符串的格式存储
$redis->set('user:userid:1', json_encode([
'userid' => 1,
'username' => 'Jack',
'birth' => '1986-12-06',
'contacts' => [
['mobile' => '13570845544', 'email' => 'aaa@qq.com'],
['mobile' => '13800138000', 'email' => 'aaa@qq.com']
]
]));
$redis->set('user:userid:2', json_encode([
'userid' => 2,
'username' => 'Jason',
'birth' => '1980-08-01',
'contacts' => [
['mobile' => '13800138120', 'email' => 'aaa@qq.com'],
['mobile' => '13800138119', 'email' => 'aaa@qq.com'],
['mobile' => '13800138119', 'email' => 'aaa@qq.com']
]
]));
$redis->set('user:userid:3', json_encode([
'userid' => 3,
'username' => 'Jim',
'birth' => '1977-01-25',
'contacts' => [
['mobile' => '13000138120', 'email' => 'aaa@qq.com'],
['mobile' => '13500138119', 'email' => 'aaa@qq.com'],
['mobile' => '13600138120', 'email' => 'aaa@qq.com'],
['mobile' => '13900138119', 'email' => 'aaa@qq.com']
]
]));
*/
}本篇讲解完毕 未经许可，不得商用出版发行！