160 lines
4.3 KiB
Markdown
160 lines
4.3 KiB
Markdown
# 手机号 → 账号ID → 角色ID 检索方法
|
||
|
||
## 数据关系
|
||
|
||
```
|
||
手机号 (明文)
|
||
│ XXTEA 加密
|
||
▼
|
||
tel_encrypt (密文) account_id
|
||
│ │
|
||
▼ ▼
|
||
vala_app_account ──────────► vala_app_character
|
||
(账号表) 1:N 关联 (角色表)
|
||
```
|
||
|
||
- **一个账号** (`vala_app_account`) 可以有 **多个角色** (`vala_app_character`)
|
||
- 关联字段：`vala_app_character.account_id = vala_app_account.id`
|
||
|
||
## 数据库
|
||
|
||
| 项目 | 值 |
|
||
|------|-----|
|
||
| 数据库 | MySQL 线上环境 |
|
||
| Host | `bj-cdb-dh2fkqa0.sql.tencentcdb.com` |
|
||
| Port | `27751` |
|
||
| 库名 | `vala_user` |
|
||
| 用户 | `read_only` |
|
||
|
||
## 表结构
|
||
|
||
### vala_app_account（账号表）
|
||
|
||
| 字段 | 类型 | 说明 |
|
||
|------|------|------|
|
||
| `id` | bigint | 账号ID（主键） |
|
||
| `tel` | varchar(20) | 手机号（脱敏显示，如 `158****7007`） |
|
||
| `tel_encrypt` | varchar(100) | 手机号密文（用于精确匹配） |
|
||
|
||
### vala_app_character（角色表）
|
||
|
||
| 字段 | 类型 | 说明 |
|
||
|------|------|------|
|
||
| `id` | bigint | 角色ID（主键） |
|
||
| `account_id` | bigint | 所属账号ID |
|
||
| `nickname` | varchar(20) | 角色昵称 |
|
||
| `gender` | tinyint(1) | 性别 |
|
||
| `birthday` | varchar(50) | 生日 |
|
||
| `purchase_season_package` | text | 已购赛季包 |
|
||
|
||
## 手机号加密方式
|
||
|
||
手机号在数据库中以密文存储，加密算法为 **XXTEA + Base64 URL-safe**。
|
||
|
||
### 加密参数
|
||
|
||
| 参数 | 值 |
|
||
|------|-----|
|
||
| 算法 | XXTEA |
|
||
| 密钥 | `K1pNOZ5O5+ZqTPSHA2kzPdoNOMOGcv6g` |
|
||
| 编码 | Base64（标准 → URL-safe：`+`→`-`, `/`→`_`, `=`→`.`） |
|
||
|
||
### Python 加密代码
|
||
|
||
```python
|
||
import xxtea
|
||
import base64
|
||
|
||
KEY = "K1pNOZ5O5+ZqTPSHA2kzPdoNOMOGcv6g"
|
||
|
||
def encrypt_phone(phone: str) -> str:
|
||
"""加密明文手机号，返回与数据库 tel_encrypt 字段一致的密文"""
|
||
encrypted = xxtea.encrypt(phone.encode(), KEY.encode())
|
||
result = base64.b64encode(encrypted).decode()
|
||
result = result.replace("+", "-").replace("/", "_").replace("=", ".")
|
||
return result
|
||
|
||
def decrypt_phone(encrypted: str) -> str:
|
||
"""解密 tel_encrypt 还原明文手机号"""
|
||
restored = encrypted.replace("-", "+").replace("_", "/").replace(".", "=")
|
||
decrypted = xxtea.decrypt(base64.b64decode(restored), KEY.encode())
|
||
return decrypted.decode()
|
||
```
|
||
|
||
### 加密示例
|
||
|
||
| 明文手机号 | 密文 (tel_encrypt) |
|
||
|-----------|-------------------|
|
||
| `15849377007` | `CxMOc6z56aYjE73r8OSAog..` |
|
||
|
||
## 查询步骤
|
||
|
||
### 步骤 1：加密手机号
|
||
|
||
```bash
|
||
python3 -c "
|
||
import xxtea, base64
|
||
KEY = 'K1pNOZ5O5+ZqTPSHA2kzPdoNOMOGcv6g'
|
||
phone = '15849377007'
|
||
encrypted = xxtea.encrypt(phone.encode(), KEY.encode())
|
||
result = base64.b64encode(encrypted).decode()
|
||
print(result.replace('+', '-').replace('/', '_').replace('=', '.'))
|
||
"
|
||
# 输出: CxMOc6z56aYjE73r8OSAog..
|
||
```
|
||
|
||
### 步骤 2：用密文查询账号和角色
|
||
|
||
```sql
|
||
SELECT
|
||
a.id AS account_id,
|
||
a.tel,
|
||
c.id AS character_id,
|
||
c.nickname,
|
||
c.gender,
|
||
c.birthday,
|
||
c.purchase_season_package,
|
||
c.created_at
|
||
FROM vala_app_account a
|
||
LEFT JOIN vala_app_character c ON c.account_id = a.id
|
||
WHERE a.tel_encrypt = 'CxMOc6z56aYjE73r8OSAog..';
|
||
```
|
||
|
||
### 步骤 3：解读结果
|
||
|
||
```
|
||
account_id tel character_id nickname gender birthday purchase_season_package
|
||
18279 158****7007 23600 Morris 1 2021-09-09 [16,17,18,19,20]
|
||
18279 158****7007 23686 Nathan 1 2018-03-13 [16]
|
||
```
|
||
|
||
- **账号ID**: 18279
|
||
- **角色**: 23600 (Morris)、23686 (Nathan)
|
||
- 一个账号下可能有多个角色（一个孩子一个角色）
|
||
|
||
## 完整查询脚本
|
||
|
||
`scripts/phone_encrypt.py` 已封装加密/解密/MD5 功能，可直接使用：
|
||
|
||
```python
|
||
from phone_encrypt import encrypt_phone, decrypt_phone, phone_md5
|
||
|
||
# 加密
|
||
cipher = encrypt_phone("15849377007")
|
||
# → "CxMOc6z56aYjE73r8OSAog.."
|
||
|
||
# 解密
|
||
phone = decrypt_phone(cipher)
|
||
# → "15849377007"
|
||
|
||
# MD5（用于跨系统关联）
|
||
md5 = phone_md5("15849377007")
|
||
```
|
||
|
||
## 注意事项
|
||
|
||
1. **tel 字段是脱敏的**（如 `158****7007`），不能直接用于精确匹配
|
||
2. **必须用 tel_encrypt 密文匹配**，密文由 XXTEA 加密生成
|
||
3. **一个账号可以有多个角色**，查询结果可能返回多行
|
||
4. 测试环境和线上环境的 `tel_encrypt` 值相同（加密算法一致）
|