MySQL LOAD DATA INFILE 不起作用，并且接收表没有约束

Question

我正在尝试将一些信息导入数据库。我检查了语法错误，显然一切都很好，但它一直只导入第一行。

这是我如何导入:

LOAD DATA INFILE 'importar1.csv' INTO TABLE usuarios FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(deprecated_userNode, userApellido, userDomicilioCalleyNumero, userDomicilioLocalidad, userTelefono, 
deprecated_userID, deprecated_userName, userEmail, userFechaGeneracion, userLastLogin, deprecated_userStatus, deprecated_userRoles)

这就是如何设置将接收信息的表:

CREATE TABLE usuarios(
    userID int unsigned not null auto_increment primary key,
    userEmail char(50) null,
    userApellido char(50) null,
    userNombres char(20) null,
    userDNI char(15) null,
    userPass char(65) null,
    userFechaGeneracion char(25) null COMMENT 'en formato timestamp',
    userLastLogin char(25) NULL,
    userTelefono char(200) null,
    userCelular char(200) null,
    userDomicilioCalleyNumero char(100) null,
    userDomicilioLocalidad char(80) null,
    userDomicilioProvincia char(80) null,
    userProfesion char(180) null,
    userEstadoCivil char(80) null,
    userEstudios char(80) null,
    nacionalidad char(80) null,
    userAnoticiado char(80) null,
    docFoto char(100) null,
    docDNI char(100) null,
    docCertif char(100) null,
    docVerificada int(1) not null DEFAULT '0',
    comentariosAdmin text null,
    deprecated_userName char(50) null COMMENT 'solamente para cargar los usernames del sist anterior deprecated',
    deprecated_userID int(6) null COMMENT 'userID en Drupal',
    deprecated_userStatus int(6) null COMMENT 'status 0 o 1 en Drupal para los inhabilitados',
    deprecated_userNode int(6) null COMMENT 'Nodo del perfil del usuario en Drupal',
    deprecated_userRoles char(150) null COMMENT 'los roles en drupal',
    userDatosPublicos text null,
    userTelsPublicos varchar(200) null DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

以下是 csv 文件中的一些示例信息:

"5383","Webmaster del Sitio",NULL,"0",NULL,"1","webmaster","info@mysite.org","0","1488039755","1","online1,online2,online4,online5,online6,online7,online8,webmaster,online3,online9,autor,suscriptor"
"5384","LOPEZ, Rodolfo",NULL,"Capital Federal -- Villa Crespo","4844-7484 / 15-5558-0556","4","lopez","lopez@fullzero.com.ar","1148746788","1158080258","0","ExAlumno,Directorio"
"5385","Dr. Juan Martinez",NULL,"0",NULL,"8","Dr. Juan M","juanMart@gmail.com","1149090768","1482200545","1","autor"
"5386","Isaac Smith",NULL,"0",NULL,"9","Isaac Smith1","smith@mysite.org","1149100441","0","1",NULL

每个项目都在一行中。

我已经尝试过:

LINES TERMINATED BY '\r\n'
LINES TERMINATED BY '\r'
LINES TERMINATED BY "\r\n"
LINES TERMINATED BY "\r"

我能做什么？

Answer 1

您尝试了除一个之外的所有组合，请使用换行符

LINES TERMINATED BY "\n"