如何直接通过SQLite查询导出格式统一的Apple通讯录联系人

背景信息 #

苹果通讯录数据存储在本地SQLite3数据库中，路径为：/Users/kellygold/Library/Application\ Support/AddressBook/Sources/<RANDOMSTRING>/AddressBook-v22.abcddb（其中<RANDOMSTRING>是系统生成的随机目录名）。
数据库内的ZABCDPHONENUMBER表中，ZFULLNUMBER字段的号码存储格式不统一，存在(111) 222-3333、+12223334444等多种格式，部分联系人还存在多个不同格式的号码。

现有查询语句 #

SELECT DISTINCT
    ZABCDRECORD.ZFIRSTNAME [FIRST NAME],
    ZABCDRECORD.ZLASTNAME [LAST NAME],
    ZABCDPHONENUMBER.ZFULLNUMBER [FULL NUMBER]
FROM
    ZABCDRECORD
    LEFT JOIN ZABCDPHONENUMBER ON ZABCDRECORD.Z_PK = ZABCDPHONENUMBER.ZOWNER
ORDER BY
    ZABCDRECORD.ZLASTNAME,
    ZABCDRECORD.ZFIRSTNAME,
    ZABCDPHONENUMBER.ZORDERINGINDEX ASC

期望输出格式 #

需要得到格式统一的扁平化CSV风格输出（可选择添加国家码前缀）：

FIRST NAME, LAST NAME, FULL NUMBER
asdf, fghj, 2223334444
bbbb, cccc, 12223334444

当前处理方式 #

先将SQLite查询结果导出为JSON，再通过以下脚本处理：

cat adbExport.json| jq '.[] | select(."FULL NUMBER" != null)' | sed 's/+//g' | sed 's/ //g' | sed 's/-//g' | sed 's/(//g'| sed 's/)//g' | jq '{FIRSTNAME: .FIRSTNAME, LASTNAME: .LASTNAME, FULLNUMBER: ("+1"+ .FULLNUMBER)}' | sed 's/+11/+1/g' > cleanContacts.json

解决方案：直接用SQL查询实现格式化 #

可以利用SQLite的REPLACE()函数嵌套清理号码中的非数字字符，再通过CASE语句处理国家码前缀逻辑，直接在查询中完成格式化：

完整SQL语句 #

SELECT DISTINCT
    CLEANED_CONTACTS.ZFIRSTNAME AS "FIRST NAME",
    CLEANED_CONTACTS.ZLASTNAME AS "LAST NAME",
    -- 统一处理国家码前缀
    CASE
        WHEN LENGTH(CLEANED_CONTACTS.CLEANED_NUMBER) = 10 THEN '+1' || CLEANED_CONTACTS.CLEANED_NUMBER
        WHEN LENGTH(CLEANED_CONTACTS.CLEANED_NUMBER) = 11 AND SUBSTR(CLEANED_CONTACTS.CLEANED_NUMBER, 1, 1) = '1' THEN '+1' || SUBSTR(CLEANED_CONTACTS.CLEANED_NUMBER, 2)
        ELSE CLEANED_CONTACTS.CLEANED_NUMBER
    END AS "FULL NUMBER"
FROM (
    SELECT
        ZABCDRECORD.ZFIRSTNAME,
        ZABCDRECORD.ZLASTNAME,
        -- 多层REPLACE清理所有非数字符号
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ZABCDPHONENUMBER.ZFULLNUMBER, '+', ''), ' ', ''), '-', ''), '(', ''), ')', '') AS CLEANED_NUMBER,
        ZABCDPHONENUMBER.ZORDERINGINDEX
    FROM ZABCDRECORD
    LEFT JOIN ZABCDPHONENUMBER ON ZABCDRECORD.Z_PK = ZABCDPHONENUMBER.ZOWNER
    -- 过滤空号码记录
    WHERE ZABCDPHONENUMBER.ZFULLNUMBER IS NOT NULL
) AS CLEANED_CONTACTS
ORDER BY
    CLEANED_CONTACTS.ZLASTNAME,
    CLEANED_CONTACTS.ZFIRSTNAME,
    CLEANED_CONTACTS.ZORDERINGINDEX ASC;

逻辑说明 #

1. 子查询清理号码：通过嵌套REPLACE()依次移除号码中的+、空格、-、(、)，得到纯数字的CLEANED_NUMBER，同时过滤空号码记录。
2. 外层统一国家码：用CASE语句判断清理后号码的长度和开头数字，实现原脚本中s/+11/+1/g的逻辑，避免重复添加国家码前缀。
3. 保留原有规则：维持原查询的DISTINCT去重和排序逻辑，保证输出顺序与原查询一致。

直接导出为CSV #

如果需要直接生成CSV文件，可在SQLite命令行执行以下命令（替换实际数据库路径）：

sqlite3 "/Users/kellygold/Library/Application Support/AddressBook/Sources/<RANDOMSTRING>/AddressBook-v22.abcddb" -header -csv "SELECT DISTINCT ... [上述完整SQL语句]" > clean_contacts.csv

其中-header会包含列名，-csv指定输出为CSV格式。

内容的提问来源于stack exchange，提问作者Goldfish