gpt4 book ai didi

mysql - 从csv文件导入数据库,utf8问题

转载 作者:行者123 更新时间:2023-11-30 21:44:01 27 4
gpt4 key购买 nike

所以我有一个 csv 文件,我必须每天下载它并将其更新到一个 sql 数据库中。我首先使用 phpmyadmin 获取表的所有名称,以便创建查询。假设文件有 170 行,当使用 phpmyadmin 导入时,它会导入所有 170 行。使用 sql 查询:

CREATE TABLE IF NOT EXISTS `import` (
`AnnonceurCodeListe` varchar(4) DEFAULT NULL,
`AnnonceurSocieteNom` varchar(20) DEFAULT NULL,
`AnnonceurSocieteMarque` varchar(10) DEFAULT NULL,
`AnnonceurSocieteAdresse` varchar(37) DEFAULT NULL,
`AnnonceurSocieteAdresseSuite` varchar(10) DEFAULT NULL,
`AnnonceurSocieteCodePostal` int(5) DEFAULT NULL,
`AnnonceurSocieteVille` varchar(6) DEFAULT NULL,
`AnnonceurContactsNoms` varchar(17) DEFAULT NULL,
`AnnonceurContactsTelephones` varchar(14) DEFAULT NULL,
`AnnonceurContactsTelephones2` varchar(15) DEFAULT NULL,
`AnnonceurContactsEmails` varchar(18) DEFAULT NULL,
`VehiculeIdentifiantVehicule` int(8) DEFAULT NULL,
`VehiculeReferenceVehicule` int(4) DEFAULT NULL,
`VehiculeNumeroPolice` int(4) DEFAULT NULL,
`VehiculeStatutStock` varchar(2) DEFAULT NULL,
`VehiculeAnnee` int(4) DEFAULT NULL,
`VehiculeDate1Mec` varchar(10) DEFAULT NULL,
`VehiculeGenreLibelle` varchar(18) DEFAULT NULL,
`VehiculeMarque` varchar(10) DEFAULT NULL,
`VehiculeFamille` varchar(14) DEFAULT NULL,
`VehiculeVersion` varchar(51) DEFAULT NULL,
`VehiculeModele` varchar(63) DEFAULT NULL,
`VehiculeTypeMine` varchar(15) DEFAULT NULL,
`VehiculeEnergieLibelle` varchar(10) DEFAULT NULL,
`VehiculePuissanceFiscale` int(2) DEFAULT NULL,
`VehiculePuissanceReelle` int(3) DEFAULT NULL,
`VehiculeCylindree` int(4) DEFAULT NULL,
`VehiculeNbPlaces` int(1) DEFAULT NULL,
`VehiculeNbPortes` int(1) DEFAULT NULL,
`VehiculeKilometrage` int(6) DEFAULT NULL,
`VehiculeKmGaranti` varchar(11) DEFAULT NULL,
`VehiculeCouleur` varchar(10) DEFAULT NULL,
`VehiculeBoiteLibelle` varchar(17) DEFAULT NULL,
`VehiculeNbRapports` int(1) DEFAULT NULL,
`VehiculePrixVenteTTC` int(5) DEFAULT NULL,
`VehiculePremiereMain` varchar(4) DEFAULT NULL,
`VehiculeGarantieLibelle` varchar(30) DEFAULT NULL,
`VehiculeDestinationLibelle` varchar(10) DEFAULT NULL,
`VehiculeCategorieLibelle` varchar(17) DEFAULT NULL,
`VehiculeEquipementsSerie` varchar(254) DEFAULT NULL,
`VehiculeEquipementsOption` varchar(254) DEFAULT NULL,
`VehiculeSiteLibelle` varchar(15) DEFAULT NULL,
`VehiculePhotos` varchar(189) DEFAULT NULL,
`VehiculeCo2` int(3) DEFAULT NULL,
`VehiculeCommentairePublic` varchar(10) DEFAULT NULL
) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;


LOAD DATA LOCAL INFILE 'C:/data.csv'
INTO TABLE import
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

它仅导入 88 行,其中有 64 条警告说 1262 Row 1 was truncated;它包含的数据多于输入列,此列包含特殊字符,如 éà,我尝试增加每个 varchar 的初始值,尝试更改utf,但随后它被导入,里面有奇怪的东西。我什至尝试让行的长度受到影响,但它小于 varchar 限制。我检查了有关 utf8 的多个主题和我收到的警告,但没有一个解决方案能够帮助我。提前致谢!

"AnnonceurCodeListe";"AnnonceurSocieteNom";"AnnonceurSocieteMarque";"AnnonceurSocieteAdresse";"AnnonceurSocieteAdresseSuite";"AnnonceurSocieteCodePostal";"AnnonceurSocieteVille";"AnnonceurContactsNoms";"AnnonceurContactsTelephones";"AnnonceurContactsTelephones2";"AnnonceurContactsEmails";"VehiculeIdentifiantVehicule";"VehiculeReferenceVehicule";"VehiculeNumeroPolice";"VehiculeStatutStock";"VehiculeAnnee";"VehiculeDate1Mec";"VehiculeGenreLibelle";"VehiculeMarque";"VehiculeFamille";"VehiculeVersion";"VehiculeModele";"VehiculeTypeMine";"VehiculeEnergieLibelle";"VehiculePuissanceFiscale";"VehiculePuissanceReelle";"VehiculeCylindree";"VehiculeNbPlaces";"VehiculeNbPortes";"VehiculeKilometrage";"VehiculeKmGaranti";"VehiculeCouleur";"VehiculeBoiteLibelle";"VehiculeNbRapports";"VehiculePrixVenteTTC";"VehiculePremiereMain";"VehiculeGarantieLibelle";"VehiculeDestinationLibelle";"VehiculeCategorieLibelle";"VehiculeEquipementsSerie";"VehiculeEquipementsOption";"VehiculeSiteLibelle";"VehiculePhotos";"VehiculeCo2";"VehiculeCommentairePublic"
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28754592";"2875";"2723";"ST";"2011";"05-08-2011";"Utilitaires légers";"MERCEDES";"VITO FG";"110 CDI LONG 2T8 FRIGORIFIQUE";"VITO FG 110 CDI LONG 2T8 FRIGORIFIQUE";"";"Diesel";"7";"95";"2143";"3";"4";"135224";"Garanti";"BLANC";"Manuelle";"6";"18900";"VRAI";"";"";"FOURGON 4 PORTES";"";"";"VENTE";"28754592_01_hd.jpg|28754592_02_hd.jpg|28754592_03_hd.jpg|28754592_04_hd.jpg|28754592_05_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28724592";"2872";"2720";"ST";"2010";"04-03-2010";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"235730";"Garanti";"BLANC";"Manuelle";"5";"5400";"VRAI";"";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"28724592_01_hd.jpg|28724592_02_hd.jpg|28724592_03_hd.jpg|28724592_04_hd.jpg|28724592_05_hd.jpg|28724592_06_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28894592";"2889";"2736";"ST";"2014";"16-12-2014";"Remorques";"DELCROIX";"TMC";"REMORQUE FRIGORIFIQUE BI-TEMP";"TMC REMORQUE FRIGORIFIQUE BI-TEMP";"";"";"0";"0";"0";"0";"0";"0";"Non garanti";"BLANC";"";"0";"16680";"VRAI";"";"";"Coupé cabriolet";"";"";"VENTE";"28894592_01_hd.jpg|28894592_02_hd.jpg|28894592_03_hd.jpg|28894592_04_hd.jpg|28894592_05_hd.jpg|28894592_06_hd.jpg|28894592_07_hd.jpg|28894592_08_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28634592";"2863";"2711";"ST";"2007";"08-10-2007";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"158470";"Garanti";"BLANC";"Manuelle";"5";"4990";"VRAI";"3 MOIS OU 5000 KM";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"28634592_01_hd.jpg|28634592_02_hd.jpg|28634592_03_hd.jpg|28634592_04_hd.jpg|28634592_05_hd.jpg|28634592_06_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28584592";"2858";"2706";"ST";"2009";"13-06-2009";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"168179";"Garanti";"BLANC";"Manuelle";"5";"5990";"VRAI";"";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"28584592_01_hd.jpg|28584592_02_hd.jpg|28584592_03_hd.jpg|28584592_04_hd.jpg|28584592_05_hd.jpg|28584592_06_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28614592";"2861";"2709";"ST";"2008";"03-12-2008";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"138378";"Garanti";"BLANC";"Manuelle";"5";"5990";"VRAI";"";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28524592";"2852";"2700";"ST";"2009";"17-06-2009";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"164475";"Garanti";"BLANC";"Manuelle";"5";"5990";"VRAI";"";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"28524592_01_hd.jpg|28524592_02_hd.jpg|28524592_03_hd.jpg|28524592_04_hd.jpg|28524592_05_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28444592";"2844";"2692";"ST";"2008";"03-12-2008";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"167226";"Garanti";"BLANC";"Manuelle";"5";"5490";"VRAI";"";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"28444592_01_hd.jpg|28444592_02_hd.jpg|28444592_03_hd.jpg|28444592_04_hd.jpg|28444592_05_hd.jpg|28444592_06_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28174592";"2817";"2665";"ST";"2011";"05-08-2011";"Utilitaires légers";"MERCEDES";"VITO FG";"110 CDI LONG 2T7 FRIGORIFIQUE";"VITO FG 110 CDI LONG 2T7 FRIGORIFIQUE";"";"Diesel";"7";"0";"0";"0";"0";"102462";"Garanti";"BLANC";"Manuelle";"6";"19440";"VRAI";"";"";"Fourgon";"";"";"VENTE";"28174592_01_hd.jpg|28174592_02_hd.jpg|28174592_03_hd.jpg|28174592_04_hd.jpg|28174592_05_hd.jpg|28174592_06_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28144592";"2814";"2662";"ST";"2011";"21-01-2011";"Utilitaires légers";"MERCEDES";"VITO FG";"110CDI LONG 2T7 FRIGORIFIQUE";"VITO FG 110CDI LONG 2T7 FRIGORIFIQUE";"";"Diesel";"7";"0";"0";"0";"0";"108524";"Garanti";"BLANC";"Manuelle";"6";"16500";"FAUX";"";"";"Fourgon";"";"";"VENTE";"";"0";""

"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28764592";"2876";"2724";"ST";"2011";"05-08-2011";"Utilitaires légers";"MERCEDES";"SPRINTER FG";"210 32S FRIGORIFIQUE";"SPRINTER FG 210 32S FRIGORIFIQUE";"";"Diesel";"0";"0";"0";"0";"0";"168927";"Garanti";"BLANC";"Manuelle";"6";"15000";"VRAI";"";"";"Fourgon";"";"";"VENTE";"";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28744592";"2874";"2722";"ST";"2008";"03-12-2008";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"180570";"Garanti";"BLANC";"Manuelle";"5";"4490";"VRAI";"3 MOIS OU 5000 KM";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"28744592_01_hd.jpg|28744592_02_hd.jpg|28744592_03_hd.jpg|28744592_04_hd.jpg|28744592_05_hd.jpg|28744592_06_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28884592";"2888";"2735";"ST";"2014";"16-12-2014";"Remorques";"DELCROIX";"TMC";"MONO TEMPERATURE";"TMC MONO TEMPERATURE";"";"";"0";"0";"0";"0";"0";"0";"Non garanti";"BLANC";"";"0";"14400";"VRAI";"";"";"Coupé cabriolet";"";"";"VENTE";"28884592_01_hd.jpg|28884592_02_hd.jpg|28884592_03_hd.jpg|28884592_04_hd.jpg|28884592_05_hd.jpg|28884592_06_hd.jpg|28884592_07_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28694592";"2869";"2717";"ST";"2009";"20-06-2009";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"173785";"Garanti";"BLANC";"Manuelle";"5";"5990";"VRAI";"";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"28694592_01_hd.jpg|28694592_02_hd.jpg|28694592_03_hd.jpg|28694592_04_hd.jpg|28694592_05_hd.jpg|28694592_06_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28644592";"2864";"2712";"ST";"2009";"17-06-2009";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"231449";"Garanti";"BLANC";"Manuelle";"5";"4990";"FAUX";"";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"28644592_01_hd.jpg|28644592_02_hd.jpg|28644592_03_hd.jpg|28644592_04_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28604592";"2860";"2708";"ST";"2009";"17-06-2009";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"170838";"Garanti";"BLANC";"Manuelle";"5";"5990";"VRAI";"";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"28604592_01_hd.jpg|28604592_02_hd.jpg|28604592_03_hd.jpg|28604592_04_hd.jpg|28604592_05_hd.jpg|28604592_06_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28554592";"2855";"2701";"ST";"2009";"20-06-2009";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"199905";"Garanti";"BLANC";"Manuelle";"5";"5490";"VRAI";"";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"28554592_01_hd.jpg|28554592_02_hd.jpg|28554592_03_hd.jpg|28554592_04_hd.jpg|28554592_05_hd.jpg|28554592_06_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28484592";"2848";"2696";"ST";"2007";"08-10-2007";"Utilitaires légers";"VOLKSWAGEN";"CADDY VAN";"1.9 TDI 105CH CONFORT CLIM";"CADDY VAN 1.9 TDI 105CH CONFORT CLIM";"";"Diesel";"8";"105";"1896";"0";"4";"183383";"Garanti";"BLANC";"Manuelle";"5";"4990";"VRAI";"3 MOIS OU 5000 KM";"";"FOURGONNETTE 4 P.";"";"";"VENTE";"28484592_01_hd.jpg|28484592_02_hd.jpg|28484592_03_hd.jpg|28484592_04_hd.jpg|28484592_05_hd.jpg|28484592_06_hd.jpg|28484592_07_hd.jpg";"0";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28344592";"2834";"2682";"ST";"2008";"26-08-2008";"Voitures";"BMW";"SERIE 3";"(E90) 318D 143CH CONFORT";"SERIE 3 (E90) 318D 143CH CONFORT";"";"Diesel";"7";"143";"1995";"5";"4";"176144";"Non garanti";"ANTHRACITE";"Manuelle";"6";"7590";"FAUX";"";"";"Berline";"";"";"VENTE";"28344592_01_hd.jpg|28344592_02_hd.jpg|28344592_03_hd.jpg|28344592_04_hd.jpg|28344592_05_hd.jpg|28344592_06_hd.jpg";"123";""
"ddda";"Name";"";"Addresse";"";"8888";"";"NAME";"Phone";"Phone2";"Mail";"28154592";"2815";"2663";"ST";"2011";"11-05-2011";"Utilitaires légers";"MERCEDES";"VITO FG";"110CDI LONG 2T7 FRIGORIFIQUE";"VITO FG 110CDI LONG 2T7 FRIGORIFIQUE";"";"Diesel";"7";"0";"0";"0";"0";"165278";"Garanti";"BLANC";"Manuelle";"6";"15000";"VRAI";"";"";"Fourgon";"";"";"VENTE";"";"0";""

前 10 个不是导入,下面的是。

最佳答案

我想通了为什么会出现此错误,这是在 LINES TERMINATED BY '\n' 中我必须添加 LINES TERMINATED BY '\r\n' for windows系统,感谢Vivek指出,现在一切正常!

关于mysql - 从csv文件导入数据库,utf8问题,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50306038/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com