mysql - 以编程方式使用 Node.js 读取 JSON 和 INSERT INTO mysql 的最佳方法

Question

建议一种读取 json 并将其插入 mysql 数据库的最佳方法。

示例 JSON:

{
    "PaymentOrder": [
        {
            "$": {
                "Id": "83C44C4A-7EFD-491E-913E-007A3598618F",
                "Name": "",
                "OrderNumber": "AH9NZPUI",
                "OrderType": "Recurring",
                "Amount": "1.000000",
                "CouponDiscountAmount": ".000000",
                "ChargeAmount": ".000000",
                "CurrencyCode": "USD",
                "OwnerId": "D9F48450-AB86-4ABE-B9AD-66FE3B7AEFB1",
                "OrderStatus": "Pending",
                "PaymentProcessor": "Global Collect",
                "PaymentMethodType": "Visa",
                "SoftDescriptorText": "CNext_AH9NZPUI",
                "UserPaymentInfoId": "FDD9C487-6F66-4919-B945-766699C9EF1D",
                "IsAuthorization": "1",
                "CreateTime": "2012-07-18T10:21:47.7610377",
                "UpdateTime": "2012-07-18T10:21:47.7610377",
                "IsEnabled": "1",
                "IsDeleted": "0"
            }
        },
        {
            "$": {
                "Id": "03B1600F-F92A-47BA-8B53-00B70A942E43",
                "Name": "",
                "OrderNumber": "3427BBC3",
                "OrderType": "Recurring",
                "Amount": ".000000",
                "CouponDiscountAmount": ".000000",
                "ChargeAmount": ".000000",
                "CurrencyCode": "USD",
                "OwnerId": "9A1186E4-BAC6-43D0-8C6F-B02ECD6544B5",
                "OrderStatus": "Success",
                "PaymentProcessor": "Global Collect",
                "PaymentMethodType": "Visa",
                "SoftDescriptorText": "ComputeNext Inc Order Number - 3427BBC3",
                "UserPaymentInfoId": "142C6B38-A470-40A9-B7A7-1C8CDD6FBD2C",
                "BillingHistoryId": "6BE8DF8E-4C97-492B-9407-38EBA2B942F2",
                "InvoiceDate": "2013-03-26T14:02:07.8570464",
                "IsAuthorization": "0",
                "CreateTime": "2013-03-26T18:08:19.5383118",
                "UpdateTime": "2013-03-26T18:08:19.5695118",
                "IsEnabled": "1",
                "IsDeleted": "0"
            }
        },
        {
            "$": {
                "Id": "8FC1D8EA-C388-4F31-91C7-00CB54C337A4",
                "Name": "",
                "OrderNumber": "YTM8TOKQ",
                "OrderType": "Recurring",
                "Amount": "1.000000",
                "CouponDiscountAmount": ".000000",
                "ChargeAmount": ".000000",
                "CurrencyCode": "USD",
                "OwnerId": "3702AB47-1095-4211-B844-82C47D0D9F6C",
                "OrderStatus": "Failure",
                "PaymentProcessor": "Global Collect",
                "PaymentMethodType": "Visa",
                "SoftDescriptorText": "CNext_YTM8TOKQ",
                "UserPaymentInfoId": "19193F72-14D1-4A72-9676-2493002711ED",
                "TransactionDuration": "0:0:40:809",
                "LastTransactionNumber": "HC0F4CBYMT14QP3",
                "IsAuthorization": "1",
                "CreateTime": "2012-07-26T21:15:25.8004068",
                "UpdateTime": "2012-07-26T21:16:06.6100785",
                "IsEnabled": "1",
                "IsDeleted": "0"
            }
        }
    ]
}

JSON 中的属性相当于 mysql 中的列。

Answer 1

当然，您可以用您选择的语言进行编码(您只需要一个 JSON 读取工具和一个 MySQL 客户端，但这些现在可以在所有语言中使用)，或者您可以使用 ETL 工具。

使用 ETL 的优点

源或目标格式的更改(例如，您突然需要从 CSV 文件或 XML 文件读取这些数据)或内容(字段被添加、消失或重命名)通常可以通过重新配置来解决该工具。

使用 ETL 的缺点

这是您必须管理的另一项(相当重)基础设施，有些人会认为使用 ETL 工具来完成此类任务就像用大锤杀死苍蝇一样。

尽管如此，根据您对灵 active 的要求，ETL 可能是最好/最简单的解决方案。