{"id":10214,"date":"2025-01-10T13:17:37","date_gmt":"2025-01-10T05:17:37","guid":{"rendered":"https:\/\/bohu.net\/blog\/?p=10214"},"modified":"2025-01-10T21:53:35","modified_gmt":"2025-01-10T13:53:35","slug":"%e5%af%b9sqlite%e5%b7%b2%e6%9c%89%e6%95%b0%e6%8d%ae%e6%b7%bb%e5%8a%a0%e5%a4%9a%e5%88%97%e7%9a%84%e5%94%af%e4%b8%80%e6%80%a7%e7%ba%a6%e6%9d%9f","status":"publish","type":"post","link":"https:\/\/www.bohu.net\/blog\/10214\/","title":{"rendered":"\u5bf9sqlite\u5df2\u6709\u6570\u636e,\u6dfb\u52a0\u591a\u5217\u7684\u552f\u4e00\u6027\u7ea6\u675f"},"content":{"rendered":"\n

\u4ee5\u4e00\u4e2a\u6570\u636e\u8868\u4e3a\u4f8b, \u521b\u5efasql\u5982\u4e0b:<\/p>\n\n\n\n

CREATE TABLE http_table (\n    id       INTEGER PRIMARY KEY AUTOINCREMENT,\n    Host     TEXT    DEFAULT \"\",\n    IP       TEXT    DEFAULT \"\",\n    Port     INTEGER DEFAULT (0),\n    Info     TEXT    DEFAULT \"\",\n    Uptime   TEXT    DEFAULT (datetime('now', 'localtime') ) \n);<\/code><\/pre>\n\n\n\n
\u5f53\u524d\u8be5\u8868\u5df2\u6709\u4e0d\u5c11\u6570\u636e\u5b58\u5728\uff0c\u53ea\u6709id\u4e3a\u81ea\u589e\u957f\u4e3b\u952e\uff0c\u672a\u521b\u5efa\u7ea6\u675f\u3002<\/p>\n\n\n\n
\u4e4b\u524d\u521b\u5efa\u6570\u636e\u8fc7\u7a0b\uff0c\u5148\u67e5\u8be2\uff081\uff09\uff0c\u4e0d\u5b58\u5728\u65b0\u63d2\u5165\u4e00\u6761\uff082\uff09\uff0c\u5b58\u5728\u66f4\u65b0\u8be5\u6761\u8bb0\u5f55\uff083\uff09\uff1a<\/p>\n\n\n\n
\uff081\uff09\u5148\u67e5\u8be2<\/p>\n\n\n\n
SELECT id FROM http_table WHERE Host = 'dns9.quad9.net' AND IP = '9.9.9.9' AND Port = '80' order by Uptime desc LIMIT 1;<\/code><\/pre>\n\n\n\n
\uff08\u8fd9\u91cc\uff0c\u53ef\u80fd\u662f\u591a\u6761\uff0c\u53ea\u8fd4\u56de\u4e00\u6761\uff09<\/em><\/p>\n\n\n\n
\uff082\uff09\u4e0d\u5b58\u5728\u65b0\u63d2\u5165\u4e00\u6761<\/p>\n\n\n\n
INSERT INTO http_table (Host, IP, Port, Info) VALUES ('dns9.quad9.net', '9.9.9.9', '80', 'new_info');<\/code><\/pre>\n\n\n\n
\uff083\uff09\u5b58\u5728\u66f4\u65b0\u8be5\u6761\u8bb0\u5f55<\/p>\n\n\n\n
UPDATE http_table SET Info = 'new_info' WHERE Host = 'dns9.quad9.net' AND IP = '9.9.9.9' AND Port = '80';<\/code><\/pre>\n\n\n\n
\u672c\u8eab\u8fd9\u4e2a\u66f4\u65b0\u5e76\u4e0d\u4e25\u8c28\uff0c\u6ca1\u6709\u6307\u5b9a\u552f\u4e00id\u6761\u4ef6\uff0c\u53ef\u80fd\u4f1a\u66f4\u65b0\u591a\u6761\uff0c\u4f46\u8fd9\u4e0d\u662f\u8981\u89e3\u51b3\u7684\u95ee\u9898\u3002<\/em><\/p>\n\n\n\n
\u95ee\u9898<\/strong>\u5728\u4e8e\u5728\u5927\u6279\u91cf\u7684\u6570\u636e\u63d2\u5165\u65f6\uff0c\u6bcf\u6b21\u90fd\u8981\u67e5\u8be2\uff0c\u518d\u5224\u65ad\u662f\u63d2\u5165\u8fd8\u66f4\u65b0\uff0c\u901f\u5ea6\u5f88\u6162\uff0c\u73b0\u9700\u8981\u4e00\u6761sql\u4e00\u6b21\u5b8c\u6210\u67e5\u8be2\uff0c\u5e76\u5b8c\u6210\u63d2\u5165\u6216\u66f4\u65b0\u3002<\/p>\n\n\n\n
\u5c1d\u8bd5\u4fee\u6539<\/strong>\uff1a\u5728 SQLite \u4e2d\uff0c\u53ef\u4ee5\u4f7f\u7528 INSERT INTO...ON CONFLICT<\/mark><\/code> \u5b50\u53e5\u914d\u5408 DO UPDATE<\/mark><\/code> \u52a8\u4f5c\uff0c\u5b9e\u73b0\u6839\u636e\u7279\u5b9a\u6761\u4ef6\u5b58\u5728\u5219\u66f4\u65b0\u8bb0\u5f55\uff0c\u4e0d\u5b58\u5728\u5219\u63d2\u5165\u65b0\u8bb0\u5f55\u7684\u529f\u80fd\u3002<\/p>\n\n\n\n
INSERT INTO http_table (Host, IP, Port, Info)\nVALUES ('dns9.quad9.net', '9.9.9.9', '80', 'new_info')\nON CONFLICT(Host, IP, Port) DO UPDATE SET\n    Info=excluded.Info;<\/code><\/pre>\n\n\n\n
\u89e3\u91ca\uff1a

INSERT INTO http_table (Host, IP, Port, Info) VALUES (...) \u5c1d\u8bd5\u63d2\u5165\u4e00\u6761\u65b0\u8bb0\u5f55\u3002<\/mark>

ON CONFLICT(Host, IP, Port) \u6307\u5b9a\u5f53 Host, IP, Port \u8fd9\u4e09\u4e2a\u5b57\u6bb5\u7684\u7ec4\u5408\u53d1\u751f\u51b2\u7a81\u65f6\uff08\u5373\u5df2\u5b58\u5728\u8fd9\u6837\u7684\u8bb0\u5f55\u65f6\uff09\u5e94\u91c7\u53d6\u7684\u52a8\u4f5c\u3002<\/mark>

DO UPDATE SET Info=excluded.Info \u6307\u5b9a\u5f53\u51b2\u7a81\u53d1\u751f\u65f6\uff0c\u66f4\u65b0\u5df2\u6709\u8bb0\u5f55\u7684 Info \u5b57\u6bb5\u3002excluded \u662f\u4e00\u4e2a\u7279\u6b8a\u7684\u8868\u522b\u540d\uff0c\u5b83\u5f15\u7528\u5c1d\u8bd5\u63d2\u5165\u4f46\u56e0\u51b2\u7a81\u800c\u672a\u80fd\u63d2\u5165\u7684\u503c\u3002<\/mark>

\u8fd9\u6837\uff0c\u5982\u679c\u8bb0\u5f55\u5df2\u5b58\u5728\uff0cInfo \u5b57\u6bb5\u5c06\u88ab\u66f4\u65b0\u4e3a\u65b0\u7684\u503c\uff1b\u5982\u679c\u8bb0\u5f55\u4e0d\u5b58\u5728\uff0c\u5219\u5c06\u63d2\u5165\u65b0\u7684\u8bb0\u5f55<\/mark><\/pre>\n\n\n\n
\u9996\u5148\uff0c\u4f60\u9700\u8981\u786e\u4fdd\u4f60\u7684\u8868\u6709\u4e00\u4e2a\u552f\u4e00\u7ea6\u675f\uff08UNIQUE CONSTRAINT\uff09\u6765\u68c0\u6d4b\u51b2\u7a81\u3002\u5728\u8fd9\u4e2a\u4f8b\u5b50\u4e2d\uff0c\u9700\u8981\u8bbe\u5b9a Host<\/code>, IP<\/code>, \u548c Port<\/code> \u7684\u7ec4\u5408\u662f\u552f\u4e00\u7684\uff0cUNIQUE(Host, IP, Port)<\/code>\u5426\u5219\u4f1a\u62a5\u9519\u3002<\/p>\n\n\n\n
CREATE TABLE http_table(\n    Host TEXT,\n    IP TEXT,\n    Port TEXT,\n    Info TEXT,\n    UNIQUE(Host, IP, Port)\n);<\/code><\/pre>\n\n\n\n
\u7531\u4e8e\u4e4b\u524d\u672a\u6dfb\u52a0\u552f\u4e00\u7ea6\u675f\uff0c\u6267\u884cINSERT INTO http_table ... ON CONFLICT(Host, IP, Port) DO UPDATE ...<\/code> \u4f1a\u5982\u4e0b\u62a5\u9519\uff1a<\/p>\n\n\n\n
\u6267\u884c SQL \u67e5\u8be2\u65f6\u53d1\u751f\u9519\u8bef\uff1aON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint<\/mark><\/pre>\n\n\n\n
\u89e3\u51b3\u65b9\u6cd5<\/mark><\/strong>\uff1a<\/mark>\u5efa\u7acb\u7ea6\u675f\u524d,\u6e05\u6d17\u73b0\u6709\u6570\u636e<\/mark><\/em><\/h3>\n\n\n\n
\u5728\u5efa\u7acb\u552f\u4e00\u6027\u7ea6\u675f\u4e4b\u524d\uff0c\u5982\u679c\u73b0\u6709\u6570\u636e\u4e2d\u5b58\u5728\u8fdd\u53cd\u8be5\u7ea6\u675f\u7684\u91cd\u590d\u8bb0\u5f55\uff0c\u4f60\u9700\u8981\u8fdb\u884c\u6570\u636e\u6e05\u6d17\u3002\u6570\u636e\u6e05\u6d17\u7684\u8fc7\u7a0b\u901a\u5e38\u5305\u62ec\u8bc6\u522b\u91cd\u590d\u8bb0\u5f55\u3001\u51b3\u5b9a\u5982\u4f55\u5904\u7406\u8fd9\u4e9b\u8bb0\u5f55\uff08\u5982\u5220\u9664\u3001\u5408\u5e76\u6216\u4fee\u6539\uff09\uff0c\u4ee5\u53ca\u6267\u884c\u76f8\u5e94\u7684\u64cd\u4f5c\u3002\u4ee5\u4e0b\u662f\u4e00\u4e9b\u6b65\u9aa4\u548c\u5efa\u8bae\uff0c\u5e2e\u52a9\u4f60\u6e05\u6d17\u73b0\u6709\u6570\u636e\u4ee5\u4fbf\u80fd\u591f\u6dfb\u52a0\u552f\u4e00\u6027\u7ea6\u675f\uff1a<\/p>\n\n\n\n
\u200c\u8bc6\u522b\u91cd\u590d\u8bb0\u5f55<\/strong>\u200c<\/mark>\uff1a<\/p>\n\n\n\n
\u4f7f\u7528SQL\u67e5\u8be2\u6765\u627e\u51fa\u5728Host<\/code>\u3001IP<\/code>\u548cPort<\/code>\u5217\u4e0a\u6709\u91cd\u590d\u7ec4\u5408\u7684\u8bb0\u5f55\u3002\u53ef\u4ee5\u4f7f\u7528GROUP BY<\/code>\u548cHAVING<\/code>\u5b50\u53e5\u6765\u8bc6\u522b\u8fd9\u4e9b\u91cd\u590d\u9879\u3002\u4ee5\u4e0b\u67e5\u8be2\u5c06\u627e\u51fa\u6240\u6709\u91cd\u590d\u7684Host<\/code>\u3001IP<\/code>\u548cPort<\/code>\u7ec4\u5408\u4ee5\u53ca\u5b83\u4eec\u51fa\u73b0\u7684\u6b21\u6570\uff1a<\/p>\n\n\n\n
SELECT Host, IP, Port, COUNT(*) as count FROM http_table <\/span>GROUP BY Host, IP, Port HAVING COUNT(*) > 1;<\/code><\/code><\/pre>\n\n\n\n
\u200c\u51b3\u5b9a\u5982\u4f55\u5904\u7406\u91cd\u590d\u8bb0\u5f55<\/strong>\u200c<\/mark>\uff1a<\/p>\n\n\n\n
    \n
  • \u200c\u5220\u9664\u91cd\u590d\u9879<\/strong>\u200c<\/mark>\uff1a\u5982\u679c\u91cd\u590d\u8bb0\u5f55\u6ca1\u6709\u4fdd\u7559\u4ef7\u503c\uff0c\u4f60\u53ef\u4ee5\u9009\u62e9\u5220\u9664\u5b83\u4eec\u3002\u786e\u4fdd\u5728\u5220\u9664\u4e4b\u524d\u5907\u4efd\u6570\u636e\uff0c\u4ee5\u9632\u4e07\u4e00\u3002<\/li>\n\n\n\n
  • \u200c\u5408\u5e76\u8bb0\u5f55<\/strong>\u200c<\/mark>\uff1a\u5982\u679c\u91cd\u590d\u8bb0\u5f55\u5305\u542b\u6709\u7528\u7684\u4fe1\u606f\uff0c\u4f60\u53ef\u80fd\u60f3\u8981\u5408\u5e76\u5b83\u4eec\u3002\u8fd9\u901a\u5e38\u6d89\u53ca\u66f4\u65b0\u4e00\u6761\u8bb0\u5f55\u4ee5\u5305\u542b\u6240\u6709\u76f8\u5173\u4fe1\u606f\uff0c\u5e76\u5220\u9664\u5176\u4ed6\u91cd\u590d\u8bb0\u5f55\u3002<\/li>\n\n\n\n
  • \u200c\u4fee\u6539\u8bb0\u5f55<\/strong>\u200c<\/mark>\uff1a\u5728\u67d0\u4e9b\u60c5\u51b5\u4e0b\uff0c\u4f60\u53ef\u80fd\u80fd\u591f\u901a\u8fc7\u4fee\u6539\u91cd\u590d\u8bb0\u5f55\u4e2d\u7684\u4e00\u4e2a\u6216\u591a\u4e2a\u5b57\u6bb5\u6765\u6d88\u9664\u91cd\u590d\u3002<\/li>\n<\/ul>\n\n\n\n
    \u200c\u6267\u884c\u6570\u636e\u6e05\u6d17\u64cd\u4f5c<\/strong>\u200c<\/mark>\uff1a<\/p>\n\n\n\n
    \u6839\u636e\u4f60\u7684\u51b3\u5b9a\uff0c\u7f16\u5199SQL\u8bed\u53e5\u6765\u6267\u884c\u5220\u9664\u3001\u5408\u5e76\u6216\u4fee\u6539\u64cd\u4f5c\u3002<\/p>\n\n\n\n
    \u5982\u679c\u4f60\u51b3\u5b9a\u5220\u9664\u91cd\u590d\u7684\u8bb0\u5f55\uff0c\u5e76\u4e14\u53ea\u4fdd\u7559\u6bcf\u4e2a\u7ec4\u5408\u7684\u7b2c\u4e00\u6761\u8bb0\u5f55\uff0c\u4f60\u53ef\u4ee5\u4f7f\u7528\u4ee5\u4e0b\u67e5\u8be2\uff08\u8bf7\u6ce8\u610f\uff0c\u8fd9\u4e2a\u67e5\u8be2\u5047\u8bbe\u4f60\u6709\u4e00\u4e2a\u552f\u4e00\u6807\u8bc6\u6bcf\u6761\u8bb0\u5f55\u7684id<\/code>\u5b57\u6bb5\uff09\uff1a<\/p>\n\n\n\n
    DELETE FROM http_table <\/span>WHERE id NOT IN ( SELECT MIN(id) FROM http_table <\/span>GROUP BY Host, IP, Port );<\/span><\/code><\/code><\/pre>\n\n\n\n
    \u8fd9\u4e2a\u67e5\u8be2\u4f1a\u5220\u9664\u6bcf\u4e2aHost<\/code>\u3001IP<\/code>\u548cPort<\/code>\u7ec4\u5408\u4e2d\u9664id<\/code>\u6700\u5c0f\u7684\u4e00\u6761\u8bb0\u5f55\u4e4b\u5916\u7684\u6240\u6709\u8bb0\u5f55\u3002<\/p>\n\n\n\n
    \u200c\u9a8c\u8bc1\u6570\u636e\u6e05\u6d17\u7ed3\u679c<\/strong>\u200c<\/mark>\uff1a<\/p>\n\n\n\n
    \u5728\u6267\u884c\u6570\u636e\u6e05\u6d17\u64cd\u4f5c\u540e\uff0c\u518d\u6b21\u8fd0\u884c\u8bc6\u522b\u91cd\u590d\u8bb0\u5f55\u7684\u67e5\u8be2\uff0c\u786e\u4fdd\u6240\u6709\u91cd\u590d\u9879\u90fd\u5df2\u88ab\u5904\u7406\u3002<\/p>\n\n\n\n
    \u68c0\u67e5\u6570\u636e\u4ee5\u786e\u4fdd\u6ca1\u6709\u5176\u4ed6\u95ee\u9898\uff0c\u5982\u6570\u636e\u4e22\u5931\u6216\u4e0d\u4e00\u81f4\u3002<\/p>\n\n\n\n
    \u200c\u6dfb\u52a0\u552f\u4e00\u6027\u7ea6\u675f<\/strong>\u200c<\/mark>\uff1a<\/p>\n\n\n\n
    \u786e\u8ba4\u6570\u636e\u5df2\u7ecf\u6e05\u6d17\u5e72\u51c0\uff0c\u6ca1\u6709\u8fdd\u53cd\u552f\u4e00\u6027\u7ea6\u675f\u7684\u91cd\u590d\u8bb0\u5f55\uff0c\u53ef\u4ee5\u5b89\u5168\u5730\u6dfb\u52a0\u552f\u4e00\u6027\u7ea6\u675f\u4e86\u3002\u4f7f\u7528ALTER TABLE<\/code>\u8bed\u53e5\u6765\u6dfb\u52a0\u7ea6\u675f\uff1a<\/p>\n\n\n\n
    ALTER TABLE http_table <\/span>\nADD CONSTRAINT unique_host_ip_port UNIQUE(Host, IP, Port);<\/code><\/pre>\n\n\n\n
    \u8fd9\u65f6\u53c8\u53d1\u751f\u9519\u8bef\uff01<\/p>\n\n\n\n
    \u6267\u884c SQL \u67e5\u8be2\u65f6\u53d1\u751f\u9519\u8bef\uff1anear \"CONSTRAINT\": syntax error<\/mark><\/pre>\n\n\n\n
    \u5728SQLite\u4e2d\uff0cALTER TABLE<\/code> \u8bed\u53e5\u7528\u4e8e\u4fee\u6539\u8868\u7684\u7ed3\u6784\uff0c\u6bd4\u5982\u6dfb\u52a0\u5217\u3001\u91cd\u547d\u540d\u8868\u3001\u6dfb\u52a0\u7ea6\u675f\u7b49\u3002\u4f46\u662f\uff0cSQLite \u5728\u4f7f\u7528 ALTER TABLE<\/code> \u6dfb\u52a0\u7ea6\u675f\u65f6\u6709\u4e00\u4e9b\u9650\u5236\uff0c\u7279\u522b\u662f\u5173\u4e8e\u552f\u4e00\u6027\u7ea6\u675f\uff08UNIQUE CONSTRAINT\uff09\u3002<\/p>\n\n\n\n
    \u5728SQLite\u4e2d\uff0c\u5982\u679c\u4f60\u60f3\u8981\u7ed9\u4e00\u4e2a\u5df2\u7ecf\u5b58\u5728\u7684\u8868\u6dfb\u52a0\u4e00\u4e2a\u65b0\u7684\u552f\u4e00\u6027\u7ea6\u675f\uff0c\u5e76\u4e14\u8fd9\u4e2a\u7ea6\u675f\u6d89\u53ca\u5230\u591a\u4e2a\u5217\uff0c\u4f60\u4e0d\u80fd\u76f4\u63a5\u5728 ALTER TABLE<\/code> \u8bed\u53e5\u4e2d\u4f7f\u7528 ADD CONSTRAINT<\/code> \u8bed\u6cd5\u3002SQLite\u4e0d\u652f\u6301\u8fd9\u79cd\u76f4\u63a5\u6dfb\u52a0\u591a\u5217\u552f\u4e00\u6027\u7ea6\u675f\u7684\u65b9\u5f0f\u3002<\/p>\n\n\n\n
    \u76f8\u53cd\uff0c\u4f60\u9700\u8981\u91c7\u53d6\u4e00\u79cd\u95f4\u63a5\u7684\u65b9\u6cd5\u6765\u5b9e\u73b0\u8fd9\u4e00\u70b9\u3002\u4ee5\u4e0b\u662f\u4e00\u79cd\u53ef\u80fd\u7684\u89e3\u51b3\u65b9\u6848\uff1a<\/p>\n\n\n\n
      \n
    1. \u200c\u521b\u5efa\u4e00\u4e2a\u65b0\u8868<\/strong>\u200c<\/mark>\uff1a\u8fd9\u4e2a\u65b0\u8868\u5e94\u8be5\u5305\u542b\u4e0e\u539f\u59cb\u8868\u76f8\u540c\u7684\u5217\uff0c\u5e76\u4e14\u5305\u542b\u4f60\u60f3\u8981\u6dfb\u52a0\u7684\u552f\u4e00\u6027\u7ea6\u675f\u3002<\/li>\n\n\n\n
    2. \u200c\u590d\u5236\u6570\u636e<\/strong>\u200c<\/mark>\uff1a\u5c06\u539f\u59cb\u8868\u4e2d\u7684\u6570\u636e\u590d\u5236\u5230\u65b0\u8868\u4e2d\uff0c\u540c\u65f6\u786e\u4fdd\u6ca1\u6709\u8fdd\u53cd\u552f\u4e00\u6027\u7ea6\u675f\u7684\u6570\u636e\u88ab\u63d2\u5165\u3002<\/li>\n\n\n\n
    3. \u200c\u5220\u9664\u539f\u59cb\u8868<\/strong>\u200c<\/mark>\uff08\u53ef\u9009\uff09\uff1a\u5982\u679c\u6570\u636e\u590d\u5236\u6210\u529f\uff0c\u5e76\u4e14\u4f60\u786e\u5b9a\u65b0\u8868\u5305\u542b\u4e86\u6240\u6709\u9700\u8981\u7684\u6570\u636e\uff0c\u4f60\u53ef\u4ee5\u5220\u9664\u539f\u59cb\u8868\u3002<\/li>\n\n\n\n
    4. \u200c\u91cd\u547d\u540d\u65b0\u8868<\/strong>\u200c<\/mark>\uff1a\u5c06\u65b0\u8868\u91cd\u547d\u540d\u4e3a\u539f\u59cb\u8868\u7684\u540d\u79f0\u3002<\/li>\n<\/ol>\n\n\n\n
      \u52a1\u5fc5\u6ce8\u610f\uff1a<\/mark><\/strong>\u5907\u4efd\u6570\u636e<\/strong>\u200c<\/mark>\uff1a<\/p>\n\n\n\n
      \u5728\u8fdb\u884c\u4efb\u4f55\u6570\u636e\u4fee\u6539\u6216\u7ed3\u6784\u66f4\u6539\u4e4b\u524d\uff0c\u59cb\u7ec8\u786e\u4fdd\u4f60\u6709\u6700\u65b0\u7684\u6570\u636e\u5907\u4efd\u3002\u8fd9\u662f\u9632\u6b62\u6570\u636e\u4e22\u5931\u6216\u635f\u574f\u7684\u91cd\u8981\u6b65\u9aa4\u3002<\/p>\n\n\n\n
        \n
      1. <\/li>\n<\/ol>\n\n\n\n
        \u8bf7\u8bb0\u4f4f\uff0c\u6570\u636e\u6e05\u6d17\u662f\u4e00\u4e2a\u53ef\u80fd\u5bf9\u6570\u636e\u4ea7\u751f\u91cd\u5927\u5f71\u54cd\u7684\u64cd\u4f5c\u3002\u5728\u6267\u884c\u4efb\u4f55\u5220\u9664\u6216\u4fee\u6539\u64cd\u4f5c\u4e4b\u524d\uff0c\u52a1\u5fc5\u4ed4\u7ec6\u8003\u8651\u5e76\u5907\u4efd\u4f60\u7684\u6570\u636e\u3002<\/p>\n\n\n\n
        \u6700\u7ec8\u6267\u884c\u7684SQL\u8bed\u53e5\u662f\uff1a<\/mark><\/strong><\/p>\n\n\n\n
        --\u4fee\u6539\u539f\u66f4\u65b0\u8bed\u53e5\uff0c\u4f7f\u7528 INSERT INTO ... ON CONFLICT ... DO UPDATE \u65b9\u5f0f\uff0c\u4e00\u6b21\u67e5\u8be2\u6267\u884c\u63d2\u5165\u6216\u5220\u9664\u3002\n\nINSERT INTO http_table (Host, IP, Port, Title, Url, Info, Location, Method, Code, Tag, Server) \nVALUES ('Host', 'IP', 'Port', 'Title', 'Url', 'Info', 'Location', 'Method', 'Code', 'Tag', 'Server') \nON CONFLICT (Host, IP, Port) DO UPDATE SET Info = excluded.Info;\n\n-- \u6dfb\u52a0\u552f\u4e00\u7ea6\u675f\uff0c\u64cd\u4f5c\u6b65\u9aa4\uff1a\n\n-- \u9996\u5148\u5907\u4efd\u539f\u59cb\u7684 .db \u6587\u4ef6\n\n-- \u8bc6\u522b\u91cd\u590d\u8bb0\u5f55\nSELECT Host, IP, Port, COUNT( * ) AS count\n  FROM http_table\n GROUP BY Host, IP, Port\nHAVING COUNT( * ) > 1;\n\n-- \u5220\u9664\u91cd\u590d\u8bb0\u5f55\nDELETE FROM http_table\n      WHERE id NOT IN (\n    SELECT MIN(id) \n      FROM http_table\n     GROUP BY Host, IP, Port\n);\n\n-- \u521b\u5efa\u5305\u542b\u552f\u4e00\u6027\u7ea6\u675f\u7684\u65b0\u8868\nCREATE TABLE new_http_table (\n    id       INTEGER PRIMARY KEY AUTOINCREMENT,\n    Host     TEXT    DEFAULT \"\",\n    IP       TEXT    DEFAULT \"\",\n    Port     INTEGER DEFAULT (0),\n    Title    TEXT    DEFAULT \"\",\n    Url      TEXT    DEFAULT \"\",\n    Info     TEXT    DEFAULT \"\",\n    Location TEXT    DEFAULT \"\",\n    Method   TEXT    DEFAULT \"\",\n    Code     INTEGER DEFAULT \"\",\n    Tag      TEXT    DEFAULT \"\",\n    Server   TEXT    DEFAULT \"\",\n    Uptime   TEXT    DEFAULT (datetime('now', 'localtime') ) ,\n    UNIQUE(Host, IP, Port)\n);\n\n-- \u590d\u5236\u6570\u636e\u5230\u65b0\u8868\uff08\u786e\u4fdd\u6ca1\u6709\u91cd\u590d\u9879\uff09\nINSERT INTO new_http_table (id, Host, IP, Port, Title, Url, Info, Location, Method, Code, Tag, Server, Uptime)\n\tSELECT id, Host, IP, Port, Title, Url, Info, Location, Method, '', Tag, Server, Uptime\n\tFROM http_table\n\tWHERE (Host, IP, Port) IN (SELECT Host, IP, Port\n\t\t\t\t\t\t\t\tFROM http_table\n\t\t\t\t\t\t\t\tGROUP BY Host, IP, Port\n\t\t\t\t\t\t\t\tHAVING COUNT( * ) = 1);\n\n-- \u67e5\u8be2\u5bf9\u6bd4\u65b0\u65e7\u4e24\u4e2a\u8868\u6570\u636e\u603b\u6570\nSELECT (SELECT COUNT( * ) \n          FROM http_table) AS old_count, (SELECT COUNT( * ) \n                                                    FROM new_http_table) AS new_count;\n\n-- \uff08\u53ef\u9009\uff09\u5220\u9664\u539f\u59cb\u8868\n--DROP TABLE http_table;\n\n-- \u91cd\u547d\u540d\u539f\u59cb\u8868\u7684\u540d\u79f0\nALTER TABLE http_table RENAME TO back_http_table;\n\n-- \u91cd\u547d\u540d\u65b0\u8868\u4e3a\u539f\u59cb\u8868\u7684\u540d\u79f0\nALTER TABLE new_http_table RENAME TO http_table;\n\n-- \uff08\u53ef\u9009\uff09\u5220\u9664\u5907\u4efd\u8868\n--DROP TABLE back_http_table;\n\n--\u91ca\u653e\u7a7a\u95f2\u5360\u7528\uff08\u7c7b\u4f3c\u6536\u7f29\u6570\u636e\u5e93\uff09\nVACUUM;\n\n--\u68c0\u67e5\u6570\u636e\u5e93\u5b8c\u6574\u6027\nPRAGMA integrity_check;\n\n--\u6570\u636e\u5904\u7406\u5168\u90e8\u5b8c\u6210<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
        \u4ee5\u4e00\u4e2a\u6570\u636e\u8868\u4e3a\u4f8b, \u521b\u5efasql\u5982\u4e0b: \u5f53\u524d\u8be5\u8868\u5df2\u6709\u4e0d\u5c11\u6570\u636e\u5b58\u5728\uff0c\u53ea\u6709id\u4e3a\u81ea\u589e\u957f\u4e3b\u952e\uff0c\u672a\u521b\u5efa\u7ea6\u675f\u3002 \u4e4b\u524d\u521b\u5efa\u6570 … \u7ee7\u7eed\u9605\u8bfb\u201c\u5bf9sqlite\u5df2\u6709\u6570\u636e,\u6dfb\u52a0\u591a\u5217\u7684\u552f\u4e00\u6027\u7ea6\u675f\u201d<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,815,814,26],"tags":[811,812,645,810,809,813,808],"class_list":["post-10214","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-","category-sqlite","category-26","tag-conflict","tag-constraint","tag-sqlite","tag-unique","tag-809","tag-813","tag-808"],"_links":{"self":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/posts\/10214","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/comments?post=10214"}],"version-history":[{"count":3,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/posts\/10214\/revisions"}],"predecessor-version":[{"id":10217,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/posts\/10214\/revisions\/10217"}],"wp:attachment":[{"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/media?parent=10214"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/categories?post=10214"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bohu.net\/blog\/wp-json\/wp\/v2\/tags?post=10214"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}