基于MySQL實(shí)現(xiàn)基礎(chǔ)圖數(shù)據(jù)庫的詳細(xì)步驟
更新時間：2025年07月28日 09:11:27 作者：NPE~
圖數(shù)據(jù)庫是一種用于存儲和查詢具有復(fù)雜關(guān)系的數(shù)據(jù)的數(shù)據(jù)庫,能夠快速地查詢和處理節(jié)點(diǎn)之間的關(guān)系,本文將使用MySQL實(shí)現(xiàn)一個基礎(chǔ)的圖數(shù)據(jù)庫,有需要的小伙伴可以了解下
一、概念
圖數(shù)據(jù)庫是一種用于存儲和查詢具有復(fù)雜關(guān)系的數(shù)據(jù)的數(shù)據(jù)庫。在這種數(shù)據(jù)庫中，數(shù)據(jù)被表示為節(jié)點(diǎn)（實(shí)體）和邊（關(guān)系）。圖數(shù)據(jù)庫的核心優(yōu)勢在于能夠快速地查詢和處理節(jié)點(diǎn)之間的關(guān)系。
圖數(shù)據(jù)庫特點(diǎn)：
- 高效處理復(fù)雜關(guān)系：圖數(shù)據(jù)庫擅長處理復(fù)雜、多層級的關(guān)系，這使得它在社交網(wǎng)絡(luò)分析、推薦系統(tǒng)等領(lǐng)域具有顯著優(yōu)勢。
- 靈活的查詢語言：圖數(shù)據(jù)庫通常使用類似自然語言的查詢語言，如Gremlin或Cypher，使得查詢過程更加直觀。
但并非只有專業(yè)的圖數(shù)據(jù)庫可以實(shí)現(xiàn)圖的一些操作，比如：圖挖掘，實(shí)際也可以通過MySQL來實(shí)現(xiàn)。本文主要講解如何通過MySQL構(gòu)建圖數(shù)據(jù)存儲，當(dāng)然MySQL構(gòu)建圖結(jié)構(gòu)數(shù)據(jù)與專業(yè)圖數(shù)據(jù)庫還是有能力上的差異，比如：圖算法需要自己通過SQL實(shí)現(xiàn)、整體效率不及專業(yè)圖數(shù)據(jù)庫等。
二、應(yīng)用場景
基于MySQL實(shí)現(xiàn)圖數(shù)據(jù)庫，是通過多表關(guān)聯(lián)來實(shí)現(xiàn)操作，因此性能和整體能力肯定不及專業(yè)圖數(shù)據(jù)庫。
MySQL實(shí)現(xiàn)圖存儲最適合場景：
- 中小規(guī)模圖數(shù)據(jù)（≤10萬節(jié)點(diǎn)）
- 需要強(qiáng)事務(wù)保證的業(yè)務(wù)系統(tǒng)
- 圖查詢以1-3度關(guān)系為主
- 已有MySQL基礎(chǔ)設(shè)施且預(yù)算有限
專業(yè)圖數(shù)據(jù)庫場景：
- 大規(guī)模圖數(shù)據(jù)（≥100萬節(jié)點(diǎn)）
- 需要復(fù)雜圖算法（社區(qū)發(fā)現(xiàn)等）
- 深度路徑查詢（≥4度關(guān)系）
- 實(shí)時圖分析需求
三、實(shí)現(xiàn)
環(huán)境搭建
首先我們需要有MySQL環(huán)境，我這里為了方便就直接通過docker搭建MySQL：
docker run -d \ --name mysql8 \ --restart always \ -p 3306:3306 \ -e TZ=Asia/Shanghai \ -e MYSQL_ROOT_PASSWORD=123456 \ -v /Users/ziyi2/docker-home/mysql/data:/var/lib/mysql \ mysql:8.0
存儲結(jié)構(gòu)定義
圖主要包含節(jié)點(diǎn)、邊，因此我們這里選擇定義兩個數(shù)據(jù)表來實(shí)現(xiàn)。同時節(jié)點(diǎn)和邊都具有很多屬性，且為kv對，這里我們就采用MySQL中的JSON格式存儲。
-- 節(jié)點(diǎn)表
CREATE TABLE IF NOT EXISTS node (
node_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
properties JSON COMMENT '節(jié)點(diǎn)屬性'
);
-- 邊表
CREATE TABLE IF NOT EXISTS edge (
edge_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
source_id BIGINT NOT NULL COMMENT '源節(jié)點(diǎn)ID',
target_id BIGINT NOT NULL COMMENT '目標(biāo)節(jié)點(diǎn)ID',
properties JSON COMMENT '邊屬性',
FOREIGN KEY(source_id) REFERENCES node(node_id) ON DELETE CASCADE,
FOREIGN KEY(target_id) REFERENCES node(node_id) ON DELETE CASCADE
);
-- 索引創(chuàng)建
CREATE INDEX idx_edge_source ON edge(source_id);
CREATE INDEX idx_edge_target ON edge(target_id);
基礎(chǔ)功能
創(chuàng)建
節(jié)點(diǎn)創(chuàng)建：
-- 創(chuàng)建用戶節(jié)點(diǎn)
INSERT INTO node (properties) VALUES
('{"type": "user", "name": "張三", "age": 28, "interests": ["籃球","音樂"]}'),
('{"type": "user", "name": "李四", "age": 32, "interests": ["電影","美食"]}'),
('{"type": "user", "name": "王五", "age": 27, "interests": ["跑步","美食"]}');
邊創(chuàng)建：
-- 創(chuàng)建好友關(guān)系
INSERT INTO edge (source_id, target_id, properties) VALUES
(1, 3, '{"type": "friend", "since": "2023-01-01"}'),
(2, 3, '{"type": "friend", "since": "2023-01-01"}');
查詢
根據(jù)節(jié)點(diǎn)屬性查詢節(jié)點(diǎn)
SELECT * from node where properties->>'$.name' = '張三';
查詢某個節(jié)點(diǎn)關(guān)聯(lián)的另一個節(jié)點(diǎn)
-- 查詢張三的好友 SELECT n2.node_id, n2.properties->>'$.name' AS friend_name FROM edge e JOIN node n1 ON e.source_id = n1.node_id JOIN node n2 ON e.target_id = n2.node_id WHERE n1.properties->>'$.name' = '張三' AND e.properties->>'$.type' = 'friend';
查詢兩個節(jié)點(diǎn)的公共節(jié)點(diǎn)。查詢共同好友，因?yàn)閺埲?、王五是好友，李四、王五是好友，所以張三跟李四的共同好友就是王?/p>
-- 查詢共同好友 SELECT n3.properties->>'$.name' AS common_friend FROM edge e1 JOIN edge e2 ON e1.target_id = e2.target_id JOIN node n1 ON e1.source_id = n1.node_id JOIN node n2 ON e2.source_id = n2.node_id JOIN node n3 ON e1.target_id = n3.node_id WHERE n1.properties->>'$.name' = '張三' AND n2.properties->>'$.name' = '李四' AND e1.properties->>'$.type' = 'friend' AND e2.properties->>'$.type' = 'friend';
遞歸
查找某個節(jié)點(diǎn)關(guān)聯(lián)的所有節(jié)點(diǎn)，類似與Neo4j中的Expand展開。
-- 遞歸查找所有關(guān)聯(lián)節(jié)點(diǎn)
WITH RECURSIVE node_path AS (
SELECT
source_id,
target_id,
properties,
1 AS depth
FROM edge
WHERE source_id = 1
UNION ALL
SELECT
np.source_id,
e.target_id,
e.properties,
np.depth + 1
FROM node_path np
JOIN edge e ON np.target_id = e.source_id
WHERE np.depth < 5 -- 控制最大深度
)
SELECT * FROM node_path;
效果：
更新
-- 更新節(jié)點(diǎn)已有屬性值【更新完之后查詢效果】 SELECT * from node where properties->>'$.name' = '張三'; UPDATE node SET properties = JSON_SET(properties, '$.age', 29) WHERE properties->>'$.name' = '張三'; -- 新增節(jié)點(diǎn)屬性：添加新興趣 UPDATE node SET properties = JSON_ARRAY_APPEND(properties, '$.interests', '游泳') WHERE properties->>'$.name' = '張三'; SELECT * from node where properties->>'$.name' = '張三';
刪除
-- 刪除關(guān)系 DELETE FROM edge WHERE source_id = (SELECT node_id FROM node WHERE properties->>'$.name' = '張三') AND target_id = (SELECT node_id FROM node WHERE properties->>'$.name' = '王五'); -- 刪除節(jié)點(diǎn)及其關(guān)系 DELETE FROM node WHERE properties->>'$.name' = '張三';
下面演示刪除關(guān)系過程，刪除節(jié)點(diǎn)同理：
1.刪除之前
select * from edge WHERE source_id = (SELECT node_id FROM node WHERE properties->>'$.name' = '張三') AND target_id = (SELECT node_id FROM node WHERE properties->>'$.name' = '王五');
2. 執(zhí)行SQL刪除后
-- 刪除關(guān)系 DELETE FROM edge WHERE source_id = (SELECT node_id FROM node WHERE properties->>'$.name' = '張三') AND target_id = (SELECT node_id FROM node WHERE properties->>'$.name' = '王五');
圖算法實(shí)現(xiàn)
1. 度中心性算法
度中心性算法（Degree Centrality）
- 介紹：中心性是刻畫節(jié)點(diǎn)中心性的最直接度量指標(biāo)。節(jié)點(diǎn)的度是指一個節(jié)點(diǎn)連接的邊的數(shù)量，一個 節(jié)點(diǎn)的度越大就意味著這個節(jié)點(diǎn)的度中心性越高，該節(jié)點(diǎn)在網(wǎng)絡(luò)中就越重要。對于有向圖，還 要分別考慮出度/入度/出入度。
- 計(jì)算：統(tǒng)計(jì)節(jié)點(diǎn)連接的邊數(shù)量。
- 應(yīng)用：計(jì)算某個領(lǐng)域的KOL關(guān)鍵人物，頭部商家、用戶、up主…
數(shù)據(jù)構(gòu)造：
-- 刪除之前數(shù)據(jù)，避免用戶數(shù)據(jù)重復(fù)等
DELETE FROM edge;
DELETE FROM node;
ALTER TABLE node AUTO_INCREMENT = 1;
ALTER TABLE edge AUTO_INCREMENT = 1;
-- 創(chuàng)建用戶節(jié)點(diǎn)
INSERT INTO node (properties) VALUES
('{"type":"user","name":"張三","title":"科技博主"}'),
('{"type":"user","name":"李四","title":"美食達(dá)人"}'),
('{"type":"user","name":"王五","title":"旅行攝影師"}'),
('{"type":"user","name":"趙六","title":"投資專家"}'),
('{"type":"user","name":"錢七","title":"健身教練"}'),
('{"type":"user","name":"周八","title":"寵物博主"}'),
('{"type":"user","name":"吳九","title":"歷史學(xué)者"}');
-- 創(chuàng)建關(guān)注關(guān)系
INSERT INTO edge (source_id, target_id, properties) VALUES
-- 張三被關(guān)注關(guān)系
(2,1, '{"type":"follow","timestamp":"2023-01-10"}'),
(3,1, '{"type":"follow","timestamp":"2023-01-12"}'),
(4,1, '{"type":"follow","timestamp":"2023-01-15"}'),
(5,1, '{"type":"follow","timestamp":"2023-01-18"}'),
-- 李四被關(guān)注關(guān)系
(1,2, '{"type":"follow","timestamp":"2023-01-20"}'),
(3,2, '{"type":"follow","timestamp":"2023-01-22"}'),
(6,2, '{"type":"follow","timestamp":"2023-01-25"}'),
-- 王五被關(guān)注關(guān)系
(1,3, '{"type":"follow","timestamp":"2023-02-01"}'),
(7,3, '{"type":"follow","timestamp":"2023-02-05"}'),
-- 趙六被關(guān)注關(guān)系
(4,4, '{"type":"follow","timestamp":"2023-02-10"}'); -- 自關(guān)注（特殊情況）
度中心性算法實(shí)現(xiàn)：
-- 計(jì)算用戶被關(guān)注度（入度中心性）
SELECT
n.node_id,
n.properties->>'$.name' AS user_name,
n.properties->>'$.title' AS title,
COUNT(e.edge_id) AS follower_count,
-- 計(jì)算標(biāo)準(zhǔn)化中心性（0-1范圍）
ROUND(COUNT(e.edge_id) / (SELECT COUNT(*)-1 FROM node WHERE properties->>'$.type'='user'), 3) AS normalized_centrality
FROM node n
LEFT JOIN edge e ON n.node_id = e.target_id
AND e.properties->>'$.type' = 'follow'
WHERE n.properties->>'$.type' = 'user'
GROUP BY n.node_id
ORDER BY follower_count DESC;
效果：
2. 相似度算法
圖場景中相似度算法主流的主要包含：余弦相似度、杰卡德相似度。這里主要介紹下Jaccard相似度算法。
- 杰卡德相似度（Jaccard Similarity）
- 介紹：節(jié)點(diǎn)A和節(jié)點(diǎn)B的杰卡德相似度定義為，節(jié)點(diǎn)A鄰居和節(jié)點(diǎn)B鄰居的交集節(jié)點(diǎn)數(shù)量除以并集節(jié)點(diǎn) 數(shù)量。Jaccard系數(shù)計(jì)算的是兩個節(jié)點(diǎn)的鄰居集合的重合程度，以此來衡量兩個節(jié)點(diǎn)的相似度。
- 計(jì)算：計(jì)算兩個節(jié)點(diǎn)鄰居集合的交集數(shù)量和并集數(shù)量，然后再相除。公式：|A ∩ B| / (|A| + |B| - |A ∩ B|)
- 應(yīng)用：共同好友推薦、電商商品推薦猜你喜歡
數(shù)據(jù)構(gòu)造：
-- 清理之前數(shù)據(jù)，避免混淆
DELETE FROM edge;
DELETE FROM node;
ALTER TABLE node AUTO_INCREMENT = 1;
ALTER TABLE edge AUTO_INCREMENT = 1;
-- 創(chuàng)建用戶節(jié)點(diǎn)（包含風(fēng)險標(biāo)記）
INSERT INTO node (properties) VALUES
('{"type":"user","name":"張三","phone":"13800138000","risk_score":5,"register_time":"2023-01-01"}'),
('{"type":"user","name":"李四","phone":"13900139000","risk_score":85,"register_time":"2023-01-05"}'), -- 黑產(chǎn)用戶
('{"type":"user","name":"王五","phone":"13700137000","risk_score":92,"register_time":"2023-01-10"}'), -- 黑產(chǎn)用戶
('{"type":"user","name":"趙六","phone":"13600136000","risk_score":15,"register_time":"2023-01-15"}'),
('{"type":"user","name":"錢七","phone":"13500135000","risk_score":8,"register_time":"2023-01-20"}'),
('{"type":"user","name":"孫八","phone":"13400134000","risk_score":95,"register_time":"2023-01-25"}'); -- 黑產(chǎn)用戶
-- 創(chuàng)建設(shè)備節(jié)點(diǎn)
INSERT INTO node (properties) VALUES
('{"type":"device","device_id":"D001","model":"iPhone12","os":"iOS14"}'),
('{"type":"device","device_id":"D002","model":"HuaweiP40","os":"Android10"}'),
('{"type":"device","device_id":"D003","model":"Xiaomi11","os":"Android11"}'),
('{"type":"device","device_id":"D004","model":"OPPOReno5","os":"Android11"}');
-- 創(chuàng)建銀行卡節(jié)點(diǎn)
INSERT INTO node (properties) VALUES
('{"type":"bank_card","card_no":"622588******1234","bank":"招商銀行"}'),
('{"type":"bank_card","card_no":"622848******5678","bank":"農(nóng)業(yè)銀行"}'),
('{"type":"bank_card","card_no":"622700******9012","bank":"建設(shè)銀行"}'),
('{"type":"bank_card","card_no":"622262******3456","bank":"交通銀行"}');
-- 創(chuàng)建IP地址節(jié)點(diǎn)
INSERT INTO node (properties) VALUES
('{"type":"ip","ip_address":"192.168.1.101","location":"廣東深圳"}'),
('{"type":"ip","ip_address":"192.168.2.202","location":"浙江杭州"}'),
('{"type":"ip","ip_address":"192.168.3.303","location":"江蘇南京"}'),
('{"type":"ip","ip_address":"192.168.4.404","location":"北京朝陽"}');
-- 創(chuàng)建關(guān)聯(lián)關(guān)系
INSERT INTO edge (source_id, target_id, properties) VALUES
-- 用戶-設(shè)備關(guān)系
(1,7, '{"type":"use","first_time":"2023-01-01"}'), -- 張三使用D001
(2,7, '{"type":"use","first_time":"2023-01-05"}'), -- 李四使用D001
(2,8, '{"type":"use","first_time":"2023-01-06"}'), -- 李四使用D002
(3,8, '{"type":"use","first_time":"2023-01-10"}'), -- 王五使用D002
(3,9, '{"type":"use","first_time":"2023-01-11"}'), -- 王五使用D003
(4,10,'{"type":"use","first_time":"2023-01-15"}'), -- 趙六使用D004
(5,9, '{"type":"use","first_time":"2023-01-20"}'), -- 錢七使用D003
(6,7, '{"type":"use","first_time":"2023-01-25"}'), -- 孫八使用D001
-- 用戶-銀行卡關(guān)系
(1,11, '{"type":"bind","time":"2023-01-02"}'), -- 張三綁定銀行卡1
(2,11, '{"type":"bind","time":"2023-01-05"}'), -- 李四綁定銀行卡1
(2,12, '{"type":"bind","time":"2023-01-07"}'), -- 李四綁定銀行卡2
(3,12, '{"type":"bind","time":"2023-01-11"}'), -- 王五綁定銀行卡2
(3,13, '{"type":"bind","time":"2023-01-12"}'), -- 王五綁定銀行卡3
(4,14, '{"type":"bind","time":"2023-01-16"}'), -- 趙六綁定銀行卡4
(5,13, '{"type":"bind","time":"2023-01-21"}'), -- 錢七綁定銀行卡3
(6,11, '{"type":"bind","time":"2023-01-26"}'), -- 孫八綁定銀行卡1
-- 用戶-IP關(guān)系
(1,15, '{"type":"login","time":"2023-01-03"}'), -- 張三登錄IP1
(2,15, '{"type":"login","time":"2023-01-05"}'), -- 李四登錄IP1
(2,16, '{"type":"login","time":"2023-01-08"}'), -- 李四登錄IP2
(3,16, '{"type":"login","time":"2023-01-10"}'), -- 王五登錄IP2
(3,17, '{"type":"login","time":"2023-01-13"}'), -- 王五登錄IP3
(4,18, '{"type":"login","time":"2023-01-17"}'), -- 趙六登錄IP4
(5,17, '{"type":"login","time":"2023-01-22"}'), -- 錢七登錄IP3
(6,15, '{"type":"login","time":"2023-01-27"}'); -- 孫八登錄IP1
算法實(shí)現(xiàn)：
Jaccard相似度數(shù)學(xué)公式：|A ∩ B| / (|A| + |B| - |A ∩ B|)
-- 基于Jaccard相似度的圖相似度算法實(shí)現(xiàn)
WITH user_entities AS (
SELECT
u.node_id AS user_id,
(
SELECT JSON_ARRAYAGG(ed.target_id)
FROM edge ed
WHERE ed.source_id = u.node_id
AND ed.properties->>'$.type' = 'use'
AND ed.target_id IN (SELECT node_id FROM node WHERE properties->>'$.type' = 'device')
) AS devices,
(
SELECT JSON_ARRAYAGG(ec.target_id)
FROM edge ec
WHERE ec.source_id = u.node_id
AND ec.properties->>'$.type' = 'bind'
AND ec.target_id IN (SELECT node_id FROM node WHERE properties->>'$.type' = 'bank_card')
) AS cards,
(
SELECT JSON_ARRAYAGG(ei.target_id)
FROM edge ei
WHERE ei.source_id = u.node_id
AND ei.properties->>'$.type' = 'login'
AND ei.target_id IN (SELECT node_id FROM node WHERE properties->>'$.type' = 'ip')
) AS ips
FROM node u
WHERE u.properties->>'$.type' = 'user'
),
-- 已知黑產(chǎn)用戶
black_users AS (
SELECT node_id
FROM node
WHERE properties->>'$.type' = 'user'
AND CAST(properties->>'$.risk_score' AS UNSIGNED) > 80
),
-- 相似度計(jì)算
similarity_calc AS (
SELECT
u1.user_id AS target_user,
u2.user_id AS black_user,
-- 設(shè)備相似度 (Jaccard系數(shù)): |A ∩ B| / (|A| + |B| - |A ∩ B|)
CASE
WHEN u1.devices IS NULL OR u2.devices IS NULL
OR JSON_LENGTH(u1.devices) = 0 OR JSON_LENGTH(u2.devices) = 0
THEN 0
ELSE (
-- 分子部分: |A ∩ B| (交集的大小)
SELECT COUNT(DISTINCT d1.device_id)
FROM JSON_TABLE(u1.devices, '$[*]' COLUMNS(device_id BIGINT PATH '$')) d1
INNER JOIN JSON_TABLE(u2.devices, '$[*]' COLUMNS(device_id BIGINT PATH '$')) d2
ON d1.device_id = d2.device_id
) * 1.0 / (
-- 分母部分: (|A| + |B| - |A ∩ B|) (并集的大小)
JSON_LENGTH(u1.devices) + -- |A| 集合A的大小
JSON_LENGTH(u2.devices) - -- |B| 集合B的大小
(
-- |A ∩ B| 交集的大?。ㄔ俅斡?jì)算用于分母）
SELECT COUNT(DISTINCT d1.device_id)
FROM JSON_TABLE(u1.devices, '$[*]' COLUMNS(device_id BIGINT PATH '$')) d1
INNER JOIN JSON_TABLE(u2.devices, '$[*]' COLUMNS(device_id BIGINT PATH '$')) d2
ON d1.device_id = d2.device_id
)
)
END AS device_sim,
-- 銀行卡相似度 (Jaccard系數(shù)): |A ∩ B| / (|A| + |B| - |A ∩ B|)
CASE
WHEN u1.cards IS NULL OR u2.cards IS NULL
OR JSON_LENGTH(u1.cards) = 0 OR JSON_LENGTH(u2.cards) = 0
THEN 0
ELSE (
-- 分子部分: |A ∩ B| (交集的大小)
SELECT COUNT(DISTINCT c1.card_id)
FROM JSON_TABLE(u1.cards, '$[*]' COLUMNS(card_id BIGINT PATH '$')) c1
INNER JOIN JSON_TABLE(u2.cards, '$[*]' COLUMNS(card_id BIGINT PATH '$')) c2
ON c1.card_id = c2.card_id
) * 1.0 / (
-- 分母部分: (|A| + |B| - |A ∩ B|) (并集的大小)
JSON_LENGTH(u1.cards) + -- |A| 集合A的大小
JSON_LENGTH(u2.cards) - -- |B| 集合B的大小
(
-- |A ∩ B| 交集的大?。ㄔ俅斡?jì)算用于分母）
SELECT COUNT(DISTINCT c1.card_id)
FROM JSON_TABLE(u1.cards, '$[*]' COLUMNS(card_id BIGINT PATH '$')) c1
INNER JOIN JSON_TABLE(u2.cards, '$[*]' COLUMNS(card_id BIGINT PATH '$')) c2
ON c1.card_id = c2.card_id
)
)
END AS card_sim,
-- IP相似度 (Jaccard系數(shù)): |A ∩ B| / (|A| + |B| - |A ∩ B|)
CASE
WHEN u1.ips IS NULL OR u2.ips IS NULL
OR JSON_LENGTH(u1.ips) = 0 OR JSON_LENGTH(u2.ips) = 0
THEN 0
ELSE (
-- 分子部分: |A ∩ B| (交集的大小)
SELECT COUNT(DISTINCT i1.ip_id)
FROM JSON_TABLE(u1.ips, '$[*]' COLUMNS(ip_id BIGINT PATH '$')) i1
INNER JOIN JSON_TABLE(u2.ips, '$[*]' COLUMNS(ip_id BIGINT PATH '$')) i2
ON i1.ip_id = i2.ip_id
) * 1.0 / (
-- 分母部分: (|A| + |B| - |A ∩ B|) (并集的大小)
JSON_LENGTH(u1.ips) + -- |A| 集合A的大小
JSON_LENGTH(u2.ips) - -- |B| 集合B的大小
(
-- |A ∩ B| 交集的大小（再次計(jì)算用于分母）
SELECT COUNT(DISTINCT i1.ip_id)
FROM JSON_TABLE(u1.ips, '$[*]' COLUMNS(ip_id BIGINT PATH '$')) i1
INNER JOIN JSON_TABLE(u2.ips, '$[*]' COLUMNS(ip_id BIGINT PATH '$')) i2
ON i1.ip_id = i2.ip_id
)
)
END AS ip_sim
FROM user_entities u1
JOIN user_entities u2 ON u2.user_id IN (SELECT node_id FROM black_users)
WHERE u1.user_id NOT IN (SELECT node_id FROM black_users) -- 排除已知黑產(chǎn)
)
-- 最終結(jié)果查詢
SELECT
u.properties->>'$.name' AS target_user,
u.properties->>'$.phone' AS phone,
CAST(u.properties->>'$.risk_score' AS UNSIGNED) AS risk_score,
bu.properties->>'$.name' AS black_user,
ROUND(sc.device_sim, 3) AS device_similarity,
ROUND(sc.card_sim, 3) AS card_similarity,
ROUND(sc.ip_sim, 3) AS ip_similarity,
ROUND((sc.device_sim * 0.5 + sc.card_sim * 0.3 + sc.ip_sim * 0.2), 3) AS total_similarity,
CASE
WHEN (sc.device_sim * 0.5 + sc.card_sim * 0.3 + sc.ip_sim * 0.2) > 0.7 THEN '高風(fēng)險'
WHEN (sc.device_sim * 0.5 + sc.card_sim * 0.3 + sc.ip_sim * 0.2) > 0.4 THEN '中風(fēng)險'
ELSE '低風(fēng)險'
END AS risk_level
FROM similarity_calc sc
JOIN node u ON sc.target_user = u.node_id
JOIN node bu ON sc.black_user = bu.node_id
ORDER BY total_similarity DESC
LIMIT 5;
效果：
四、項(xiàng)目實(shí)戰(zhàn)
基于MySQL搭建的圖數(shù)據(jù)庫，模擬實(shí)現(xiàn)好友推薦功能。
數(shù)據(jù)準(zhǔn)備：
-- 創(chuàng)建用戶
INSERT INTO node (properties) VALUES
('{"type":"user","name":"張三","age":25,"city":"北京"}'),
('{"type":"user","name":"李四","age":28,"city":"北京"}'),
('{"type":"user","name":"王五","age":30,"city":"上海"}'),
('{"type":"user","name":"趙六","age":26,"city":"廣州"}'),
('{"type":"user","name":"錢七","age":27,"city":"深圳"}'),
('{"type":"user","name":"Jack","age":18,"city":"杭州"}'),
('{"type":"user","name":"Tom","age":45,"city":"貴州"}'),
('{"type":"user","name":"Mike","age":35,"city":"上海"}');
-- 創(chuàng)建好友關(guān)系
INSERT INTO edge (source_id, target_id, properties) VALUES
(1,2, '{"type":"friend"}'),
(1,3, '{"type":"friend"}'),
(2,4, '{"type":"friend"}'),
(3,5, '{"type":"friend"}'),
(4,5, '{"type":"friend"}'),
(6,7, '{"type":"friend"}'),
(7,8, '{"type":"friend"}');
具體實(shí)現(xiàn)
-- 綜合推薦算法：為張三推薦3個好友，排除現(xiàn)有好友
WITH target_user AS (
SELECT
node_id,
properties->>'$.city' AS city
FROM node
WHERE properties->>'$.name' = '張三'
),
existing_friends AS (
SELECT target_id
FROM edge
WHERE source_id = (SELECT node_id FROM target_user)
AND properties->>'$.type' = 'friend'
),
common_friends AS (
SELECT
f2.target_id AS candidate_id,
COUNT(*) AS common_friend_count
FROM edge f1
JOIN edge f2 ON f1.target_id = f2.source_id
WHERE f1.source_id = (SELECT node_id FROM target_user)
AND f2.target_id NOT IN (SELECT target_id FROM existing_friends) -- 排除現(xiàn)有好友
AND f2.target_id != (SELECT node_id FROM target_user) -- 排除自己
AND f1.properties->>'$.type' = 'friend'
AND f2.properties->>'$.type' = 'friend'
GROUP BY f2.target_id
),
same_city AS (
SELECT
n.node_id AS candidate_id,
1 AS same_city_score
FROM node n
WHERE n.properties->>'$.city' = (SELECT city FROM target_user)
AND n.node_id != (SELECT node_id FROM target_user)
AND n.node_id NOT IN (SELECT target_id FROM existing_friends) -- 排除現(xiàn)有好友
),
final_candidates AS (
SELECT
cf.candidate_id,
COALESCE(cf.common_friend_count, 0) AS common_friends,
COALESCE(sc.same_city_score, 0) AS same_city,
COALESCE(cf.common_friend_count, 0) * 0.6 +
COALESCE(sc.same_city_score, 0) * 0.4 AS recommendation_score
FROM common_friends cf
LEFT JOIN same_city sc ON cf.candidate_id = sc.candidate_id
UNION ALL
SELECT
sc.candidate_id,
0 AS common_friends,
sc.same_city_score AS same_city,
sc.same_city_score * 0.4 AS recommendation_score
FROM same_city sc
WHERE sc.candidate_id NOT IN (SELECT candidate_id FROM common_friends)
)
SELECT
n.properties->>'$.name' AS recommended_name,
fc.common_friends,
fc.same_city,
fc.recommendation_score
FROM final_candidates fc
JOIN node n ON fc.candidate_id = n.node_id
ORDER BY recommendation_score DESC
LIMIT 3;
效果展示
可以看到最后只給張三推薦了趙六和錢七，并沒有推薦Tom、Jack等用戶。
到此這篇關(guān)于基于MySQL實(shí)現(xiàn)基礎(chǔ)圖數(shù)據(jù)庫的詳細(xì)步驟的文章就介紹到這了,更多相關(guān)MySQL圖數(shù)據(jù)庫內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql配置模板(my-*.cnf)參數(shù)詳細(xì)說明
這篇文章主要介紹了mysql配置模板就是mysql的配置文件參數(shù)說明,需要的朋友可以參考下2015-01-01
在Windows系統(tǒng)上使用壓縮歸檔文件安裝MySQL的步驟
這篇文章主要介紹了在Windows系統(tǒng)上使用壓縮歸檔文件安裝MySQL的步驟,非常不錯，具有一定的參考借鑒加載，需要的朋友可以參考下2018-06-06
這篇文章主要介紹了mysql語句性能分析工具之profiling用法,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12
初學(xué)者比較容易理解的多表查詢，就是直接查詢兩張表的字段，當(dāng)然，星號可以改為對應(yīng)的字段名，這篇文章主要介紹了Javaweb?MySQL中的多表查詢,需要的朋友可以參考下2023-07-07
order?by?+?limit分頁時數(shù)據(jù)重復(fù)問題及解決方法
這篇文章主要介紹了order?by?+?limit分頁時數(shù)據(jù)重復(fù),本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-03-03
centos7中如何利用crontab進(jìn)行mysql定時備份
crontab是一個命令,常見于Unix和類Unix的操作系統(tǒng)之中,用于設(shè)置周期性被執(zhí)行的指令,下面這篇文章主要給大家介紹了關(guān)于centos7中如何利用crontab進(jìn)行mysql定時備份的相關(guān)資料,需要的朋友可以參考下2022-02-02
索引(Index)是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu),是對表中一列或多列值進(jìn)行排序的結(jié)構(gòu),下面這篇文章主要給大家介紹了關(guān)于MySQL索引的一些常見面試題,文中介紹的非常詳細(xì),需要的朋友可以參考下2023-02-02
這篇文章主要介紹了MySQL更改默認(rèn)字符集為utf-8的全過程，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-12-12
今天小編就為大家分享一篇關(guān)于B-Tree的性質(zhì)介紹，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-01-01
本文給大家詳細(xì)介紹了Centos6.9安裝Mysql5.7.18的步驟，非常不錯，具有參考借鑒價值，需要的朋友參考下吧2017-06-06