php - LOAD DATA INFILE 不适用于在字段数据中包含编码 JSON 值的 CSV

Question

我有数百万条记录要插入到 mysql 表中。为了快速将数据迁移到表中，我尝试使用 LOAD DATA INFILE 概念。

以下是 CSV 文件中数据的格式（部分字段包含 JSON 数据）。

"1299","1420070400000","35520","1420070400000","0","0","0","2","null","{""types"": [3]}","{""types"": [2]}","{""type"": 0, ""color"": 0, ""types"": [1], ""colors"": [2]}","null","null","null","null","{""type"": 0, ""types"": [], ""sexDrive"": 0, ""sexDrives"": [1]}","null","{""results"": [2]}","{""types"": [3]}","null","null","null","{""types"": [5]}","null","null","null","null","null","null","null","null","2020-01-30 11:48:07","2020-01-30 11:48:07"

"1300","1420070400000","48219","1420070400000","0","0","0","2","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","2020-01-30 11:48:07","2020-01-30 11:48:07"

"1301","1420070400000","34938","1420070400000","0","0","0","2","null","null","null","{""type"": 0, ""color"": 0, ""types"": [1], ""colors"": [3]}","null","null","{""types"": [2]}","{""pain"": 0, ""pains"": [2], ""otherPains"": [3, 4, 5, 6], ""breastSymptom"": false, ""breastSymptoms"": [1]}","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","null","2020-01-30 11:48:07","2020-01-30 11:48:07"

当我尝试执行代码时，它显示如下错误 -

SQLSTATE[22032]: <>: 3140 无效的 JSON 文本：“在对象成员之后缺少逗号或 '}'。” 在“user_day_record.notesObj”列值的第 42 位

基本上，当我尝试在 CSV 中执行没有 JSON 值的相同命令时，它可以完美运行。但是对于文件中的 JSON 数据（在某些字段中），它会给出错误。

这是命令 -

LOAD DATA LOCAL INFILE '$file' INTO TABLE user_day_record CHARACTER SET UTF8 FIELDS TERMINATED BY ', ' LINES TERMINATED BY '\n' 

任何人都可以帮助我使用 CSV 文件中的字段中的 json 值成功执行命令吗？

更新 -

这是user_day_record表的结构 -

标签： phpmysqljsoncsvload-data-infile

Answer 1

这是加载包含 JSON 对象的 CSV 文件的解决方案。以下是我们必须用来导入的额外选项

由 ',' 终止的字段 可选地由 '\"' 包围 由 '\'
转义 由 '\"' 转义 由 '\n' 终止的行

*************************** 1. row ***************************
       Table: user_day_record
Create Table: CREATE TABLE `user_day_record` (
  `dayRecordld` bigint(20) NOT NULL AUTO_INCREMENT,
  `id` varchar(191) NOT NULL,
  `userld` int(11) NOT NULL,
  `daylnMillis` bigint(20) NOT NULL,
  `periodStatusActive` tinyint(4) NOT NULL DEFAULT '0',
  `ignoreB8T` tinyint(4) NOT NULL DEFAULT '0',
  `isOCOff` tinyint(4) NOT NULL DEFAULT '0',
  `version` int(11) DEFAULT NULL,
  `bbt` json DEFAULT NULL,
  `cervicalMucusObj` json DEFAULT NULL,
  `cervicalPositionObj` json DEFAULT NULL,
  `spotting` json DEFAULT NULL,
  `periodStrength` json DEFAULT NULL,
  `temperaturelnterferenceObj` json DEFAULT NULL,
  `hygieneProductsObj` json DEFAULT NULL,
  `pain` json DEFAULT NULL,
  `intercourse` json DEFAULT NULL,
  `ovulationTestObj` json DEFAULT NULL,
  `pregnancyTestObj` json DEFAULT NULL,
  `illnessObj` json DEFAULT NULL,
  `medicationObj` json DEFAULT NULL,
  `notesObj` json DEFAULT NULL,
  `sleep` json DEFAULT NULL,
  `emotionsObj` json DEFAULT NULL,
  `activitiesObj` json DEFAULT NULL,
  `oralContraceptives` json DEFAULT NULL,
  `digestion` json DEFAULT NULL,
  `pExams` json DEFAULT NULL,
  `pMyBaby` json DEFAULT NULL,
  `pUltrasound` json DEFAULT NULL,
  `pVitamin` json DEFAULT NULL,
  `pWeight` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`dayRecordld`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> load data local infile '/var/root/temp_Data.csv' into table user_day_record fields terminated by ',' optionally enclosed by '\"'  escaped by '\\' escaped by '\"' lines terminated by '\n';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql>
mysql> select * from user_day_record\G
*************************** 1. row ***************************
               dayRecordld: 1299
                        id: 1420070400000
                    userld: 35520
               daylnMillis: 1420070400000
        periodStatusActive: 0
                 ignoreB8T: 0
                   isOCOff: 0
                   version: 2
                       bbt: null
          cervicalMucusObj: {"types": [3]}
       cervicalPositionObj: {"types": [2]}
                  spotting: {"type": 0, "color": 0, "types": [1], "colors": [2]}
            periodStrength: null
temperaturelnterferenceObj: null
        hygieneProductsObj: null
                      pain: null
               intercourse: {"type": 0, "types": [], "sexDrive": 0, "sexDrives": [1]}
          ovulationTestObj: null
          pregnancyTestObj: {"results": [2]}
                illnessObj: {"types": [3]}
             medicationObj: null
                  notesObj: null
                     sleep: null
               emotionsObj: {"types": [5]}
             activitiesObj: null
        oralContraceptives: null
                 digestion: null
                    pExams: null
                   pMyBaby: null
               pUltrasound: null
                  pVitamin: null
                   pWeight: null
                created_at: 2020-01-30 11:48:07
                updated_at: 2020-01-30 11:48:07
*************************** 2. row ***************************
               dayRecordld: 1300
                        id: 1420070400000
                    userld: 48219
               daylnMillis: 1420070400000
        periodStatusActive: 0
                 ignoreB8T: 0
                   isOCOff: 0
                   version: 2
                       bbt: null
          cervicalMucusObj: null
       cervicalPositionObj: null
                  spotting: null
            periodStrength: null
temperaturelnterferenceObj: null
        hygieneProductsObj: null
                      pain: null
               intercourse: null
          ovulationTestObj: null
          pregnancyTestObj: null
                illnessObj: null
             medicationObj: null
                  notesObj: null
                     sleep: null
               emotionsObj: null
             activitiesObj: null
        oralContraceptives: null
                 digestion: null
                    pExams: null
                   pMyBaby: null
               pUltrasound: null
                  pVitamin: null
                   pWeight: null
                created_at: 2020-01-30 11:48:07
                updated_at: 2020-01-30 11:48:07
*************************** 3. row ***************************
               dayRecordld: 1301
                        id: 1420070400000
                    userld: 34938
               daylnMillis: 1420070400000
        periodStatusActive: 0
                 ignoreB8T: 0
                   isOCOff: 0
                   version: 2
                       bbt: null
          cervicalMucusObj: null
       cervicalPositionObj: null
                  spotting: {"type": 0, "color": 0, "types": [1], "colors": [3]}
            periodStrength: null
temperaturelnterferenceObj: null
        hygieneProductsObj: {"types": [2]}
                      pain: {"pain": 0, "pains": [2], "otherPains": [3, 4, 5, 6], "breastSymptom": false, "breastSymptoms": [1]}
               intercourse: null
          ovulationTestObj: null
          pregnancyTestObj: null
                illnessObj: null
             medicationObj: null
                  notesObj: null
                     sleep: null
               emotionsObj: null
             activitiesObj: null
        oralContraceptives: null
                 digestion: null
                    pExams: null
                   pMyBaby: null
               pUltrasound: null
                  pVitamin: null
                   pWeight: null
                created_at: 2020-01-30 11:48:07
                updated_at: 2020-01-30 11:48:07
3 rows in set (0.00 sec)

mysql>```