如何将UTF-8编码的CSV文件导入latin-1字符集数据库

环境说明 #

• 待导入CSV文件由供应商提供，已通过记事本另存校验，确认编码为UTF-8
• 当前使用不可修改配置的共享主机，phpMyAdmin显示服务器默认字符集为cp1252 West European (latin1)；相同业务代码在UTF-8字符集的其他服务器上可正常运行，判定该字符集配置为问题诱因
• 服务器连接排序规则为utf8mb4_unicode_ci，待导入的数据表及表内所有字段排序规则均设置为utf8_general_ci

问题现象 #

• 自行编写的带文件上传表单的PHP CSV导入脚本运行时，约50%的数据行可正常导入，但字段包含引号、撇号、特殊字符时会触发大量报错
• 代码打印的数据库连接初始字符集为 Latin 1
• 直接使用phpMyAdmin自带的导入功能上传相同CSV文件，所有数据均可正常导入、展示，无异常

排查过程 #

• 初期误判问题根源为编码不匹配，尝试将UTF-8编码的CSV文件转码为cp1252，但公开可查的转码案例均为cp1252转UTF-8，未找到可行的反向转码方案，排查一度停滞
• 后续定位到核心报错原因：初始代码采用字符串拼接方式生成SQL语句，未对特殊字符做合规转义，容易触发SQL语法错误
• 最终使用mysqli预编译语句（prepared statements）替换原字符串拼接生成SQL的逻辑，导入异常问题彻底解决

代码示例 #

存在问题的初始代码 #

error_reporting(E_ALL);
include "conn.php";


echo "Initial character set is: " . $conn -> character_set_name();

if(isset($_POST["import"])){
    $fileName = $_FILES["file"]["tmp_name"];
mysqli_query($conn,'TRUNCATE TABLE data');
    if($_FILES["file"]["size"] > 0){
    $file = fopen($fileName, "r");
    fgets($file);  // read one line for nothing (skip header)

        while(($column = fgetcsv($file, 0, ",")) !== FALSE){
        $sqlInsert = "Insert into data (ID, Fname, Lname,  ShortBiography ) 
        
        
        values ('"  . $column[0] ."','" . $column[1] ."','" . $column[2] ."','" . $column[3] "')";
        
        $result = mysqli_query($conn, $sqlInsert);
                 * if ( ! mysqli_query( $conn, $sqlInsert ) ) {
                printf( "Error: %s\n", mysqli_error( $conn ) );
                } */ 
            if(!empty($result)){
            echo "";
            }else{
            echo "<br />Problem in importing - ".$column[1]." /<br> ";
            
            }
 
        }
    }
}
?>
<form class="form-horizontal" action="" method="post" name="uploadCsv" enctype="multipart/form-data">
<div>
<label>Choose CSV File</label>
<input type="file" name="file" accept=".csv">
<button type="submit" name="import">Import</button>
</div>
</form>

修复后可正常运行的代码 #

<?php
error_reporting(E_ALL);
include "conn.php";


echo "Initial character set is: " . $conn -> character_set_name();

if(isset($_POST["import"])){
    $fileName = $_FILES["file"]["tmp_name"];
mysqli_query($conn,'TRUNCATE TABLE data');
    if($_FILES["file"]["size"] > 0){
    $file = fopen($fileName, "r");
    fgets($file);  // read one line for nothing (skip header)

        while(($column = fgetcsv($file, 0, ",")) !== FALSE){
        $stmt = $conn->prepare("Insert into data (ID, Fname, Lname,  ShortBiography )  VALUES (?,?,?,?)");
        $stmt->bind_param("isss",$column[0], $column[1], $column[2], $column[3]);
        $stmt->execute();
        }
    }
}
?>
<form class="form-horizontal" action="" method="post" name="uploadCsv" enctype="multipart/form-data">
<div>
<label>Choose CSV File</label>
<input type="file" name="file" accept=".csv">
<button type="submit" name="import">Import</button>
</div>
</form>

内容的提问来源于stack exchange，提问作者rookiest