php与mysql的导入导出

set_time_limit(0); // 设置脚本执行时间为无限制

ini_set('memory_limit', '512M'); // 增加内存限制

$f = fopen("file.csv", "r");
while (($line = fgetcsv($f)) !== FALSE) {
    $data[] = mb_convert_encoding($line, 'UTF-8', '原始编码');
}
fclose($f);

// 在导入前检查数据
$query = "SELECT COUNT(*) FROM table WHERE primary_key = ?";
$stmt = $pdo->prepare($query);
foreach ($data as $row) {
    $stmt->execute([$row['primary_key']]);
    if ($stmt->fetchColumn() > 0) {
        // 处理重复数据
    }
}

<?php
$host = 'localhost';
$db   = 'database';
$user = 'user';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$query = "SELECT * FROM table";
$stmt = $pdo->query($query);

$fp = fopen('data.csv', 'w');
if ($fp && $stmt) {
    while ($row = $stmt->fetch()) {
        fputcsv($fp, $row);
    }
}
fclose($fp);
?>

<?php
$host = 'localhost';
$db   = 'database';
$user = 'user';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$fp = fopen('data.csv', 'r');
$data = [];
while (($row = fgetcsv($fp)) !== FALSE) {
    $data[] = $row;
}
fclose($fp);

$query = "INSERT INTO table (column1, column2) VALUES (?, ?)";
$stmt = $pdo->prepare($query);

foreach ($data as $row) {
    $stmt->execute([$row[0], $row[1]]);
}
?>