基于MySQL實現(xiàn)基礎(chǔ)圖數(shù)據(jù)庫的詳細(xì)步驟

更新時間：2025年07月28日 09:11:27 作者：NPE~

圖數(shù)據(jù)庫是一種用于存儲和查詢具有復(fù)雜關(guān)系的數(shù)據(jù)的數(shù)據(jù)庫,能夠快速地查詢和處理節(jié)點之間的關(guān)系,本文將使用MySQL實現(xiàn)一個基礎(chǔ)的圖數(shù)據(jù)庫,有需要的小伙伴可以了解下

一、概念

圖數(shù)據(jù)庫是一種用于存儲和查詢具有復(fù)雜關(guān)系的數(shù)據(jù)的數(shù)據(jù)庫。在這種數(shù)據(jù)庫中，數(shù)據(jù)被表示為節(jié)點（實體）和邊（關(guān)系）。圖數(shù)據(jù)庫的核心優(yōu)勢在于能夠快速地查詢和處理節(jié)點之間的關(guān)系。

圖數(shù)據(jù)庫特點：

高效處理復(fù)雜關(guān)系：圖數(shù)據(jù)庫擅長處理復(fù)雜、多層級的關(guān)系，這使得它在社交網(wǎng)絡(luò)分析、推薦系統(tǒng)等領(lǐng)域具有顯著優(yōu)勢。
靈活的查詢語言：圖數(shù)據(jù)庫通常使用類似自然語言的查詢語言，如Gremlin或Cypher，使得查詢過程更加直觀。

但并非只有專業(yè)的圖數(shù)據(jù)庫可以實現(xiàn)圖的一些操作，比如：圖挖掘，實際也可以通過MySQL來實現(xiàn)。本文主要講解如何通過MySQL構(gòu)建圖數(shù)據(jù)存儲，當(dāng)然MySQL構(gòu)建圖結(jié)構(gòu)數(shù)據(jù)與專業(yè)圖數(shù)據(jù)庫還是有能力上的差異，比如：圖算法需要自己通過SQL實現(xiàn)、整體效率不及專業(yè)圖數(shù)據(jù)庫等。

二、應(yīng)用場景

基于MySQL實現(xiàn)圖數(shù)據(jù)庫，是通過多表關(guān)聯(lián)來實現(xiàn)操作，因此性能和整體能力肯定不及專業(yè)圖數(shù)據(jù)庫。

MySQL實現(xiàn)圖存儲最適合場景：

中小規(guī)模圖數(shù)據(jù)（≤10萬節(jié)點）
需要強(qiáng)事務(wù)保證的業(yè)務(wù)系統(tǒng)
圖查詢以1-3度關(guān)系為主
已有MySQL基礎(chǔ)設(shè)施且預(yù)算有限

專業(yè)圖數(shù)據(jù)庫場景：

大規(guī)模圖數(shù)據(jù)（≥100萬節(jié)點）
需要復(fù)雜圖算法（社區(qū)發(fā)現(xiàn)等）
深度路徑查詢（≥4度關(guān)系）
實時圖分析需求

三、實現(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é)點、邊，因此我們這里選擇定義兩個數(shù)據(jù)表來實現(xiàn)。同時節(jié)點和邊都具有很多屬性，且為kv對，這里我們就采用MySQL中的JSON格式存儲。

-- 節(jié)點表
CREATE TABLE IF NOT EXISTS node (
    node_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    properties JSON COMMENT '節(jié)點屬性'
);

-- 邊表
CREATE TABLE IF NOT EXISTS edge (
    edge_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    source_id BIGINT NOT NULL COMMENT '源節(jié)點ID',
    target_id BIGINT NOT NULL COMMENT '目標(biāo)節(jié)點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é)點創(chuàng)建：

-- 創(chuàng)建用戶節(jié)點
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é)點屬性查詢節(jié)點

SELECT * from node
where properties->>'$.name' = '張三';

查詢某個節(jié)點關(guān)聯(lián)的另一個節(jié)點

-- 查詢張三的好友
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é)點的公共節(jié)點。查詢共同好友，因為張三、王五是好友，李四、王五是好友，所以張三跟李四的共同好友就是王五

-- 查詢共同好友
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é)點關(guān)聯(lián)的所有節(jié)點，類似與Neo4j中的Expand展開。

-- 遞歸查找所有關(guān)聯(lián)節(jié)點
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é)點已有屬性值【更新完之后查詢效果】
SELECT * from node
where properties->>'$.name' = '張三';

UPDATE node
SET properties = JSON_SET(properties, '$.age', 29)
WHERE properties->>'$.name' = '張三';

-- 新增節(jié)點屬性：添加新興趣
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é)點及其關(guān)系
DELETE FROM node WHERE properties->>'$.name' = '張三';

下面演示刪除關(guān)系過程，刪除節(jié)點同理：

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' = '王五');

圖算法實現(xiàn)

1. 度中心性算法

度中心性算法（Degree Centrality）

介紹：中心性是刻畫節(jié)點中心性的最直接度量指標(biāo)。節(jié)點的度是指一個節(jié)點連接的邊的數(shù)量，一個節(jié)點的度越大就意味著這個節(jié)點的度中心性越高，該節(jié)點在網(wǎng)絡(luò)中就越重要。對于有向圖，還要分別考慮出度/入度/出入度。
計算：統(tǒng)計節(jié)點連接的邊數(shù)量。
應(yīng)用：計算某個領(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é)點
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)注（特殊情況）

度中心性算法實現(xiàn)：

-- 計算用戶被關(guān)注度（入度中心性）
SELECT 
    n.node_id,
    n.properties->>'$.name' AS user_name,
    n.properties->>'$.title' AS title,
    COUNT(e.edge_id) AS follower_count,
    -- 計算標(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é)點A和節(jié)點B的杰卡德相似度定義為，節(jié)點A鄰居和節(jié)點B鄰居的交集節(jié)點數(shù)量除以并集節(jié)點數(shù)量。Jaccard系數(shù)計算的是兩個節(jié)點的鄰居集合的重合程度，以此來衡量兩個節(jié)點的相似度。
計算：計算兩個節(jié)點鄰居集合的交集數(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é)點（包含風(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é)點
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é)點
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é)點
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

算法實現(xiàn)：

Jaccard相似度數(shù)學(xué)公式：|A ∩ B| / (|A| + |B| - |A ∩ B|)

-- 基于Jaccard相似度的圖相似度算法實現(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
),
-- 相似度計算
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| 交集的大?。ㄔ俅斡嬎阌糜诜帜福?
                    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| 交集的大?。ㄔ俅斡嬎阌糜诜帜福?
                    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| 交集的大?。ㄔ俅斡嬎阌糜诜帜福?
                    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;

效果：

四、項目實戰(zhàn)

基于MySQL搭建的圖數(shù)據(jù)庫，模擬實現(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"}');

具體實現(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實現(xiàn)基礎(chǔ)圖數(shù)據(jù)庫的詳細(xì)步驟的文章就介紹到這了,更多相關(guān)MySQL圖數(shù)據(jù)庫內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: