MySQL的 - LOAD DATA LOCAL INFILE - 问题如果数据库表名有连字符

Question

我在PHP中使用脚本中

$sqlQueryImport = "LOAD DATA LOCAL INFILE '".$fileNewDataImportInDB."' INTO TABLE " .$sqlTableName 
     . " CHARACTER SET UTF8" 
     . " FIELDS TERMINATED BY ';'" 
     . " ENCLOSED BY '\"'"    
     . " LINES TERMINATED BY '\n'"; 

我总是错误导入CSV到数据库表中：

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tbl-test' CHARACTER SET UTF8 FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES T' at line 1"

为了测试它，我直接在phpmyadmin中将csv导入到数据库表中并且它可以正常工作。

然后我将数据库表名“tbl-test”更改为“tbl_test”并且没有错误。

不允许在数据库表名中使用“ - ”吗？或者我必须为它编写一个特殊的代码？

Answer 1

您可以尝试使用反引号转义表名称。所以，你的LOAD DATA声明是这样的：

LOAD DATA LOCAL INFILE someFile.csv INTO TABLE `tbl-test` 
CHARACTER SET UTF8 
FIELDS TERMINATED BY ';' 
ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 

的完整代码：

$sqlQueryImport = "LOAD DATA LOCAL INFILE '".$fileNewDataImportInDB."' INTO TABLE `" .$sqlTableName 
     . "` CHARACTER SET UTF8" 
     . " FIELDS TERMINATED BY ';'" 
     . " ENCLOSED BY '\"'"    
     . " LINES TERMINATED BY '\n'";