MySQL 导出某些数据的技术详解

mysqldump -u username -p database_name table_name > table_name.sql

mysqldump -u username -p database_name users --where="age > 30" > users_age_above_30.sql

SELECT id, name INTO OUTFILE '/path/to/output.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'  
LINES TERMINATED BY '\n'
FROM users;

SELECT * INTO OUTFILE '/path/to/users.csv'
FIEsLDS TERMINATED BY ',' ENCLOSED BY '"'  
LINES TERMINATED BY '\n'
FROM users;

mysqldump -u username -p --xml database_name > database_name.xml

pip install mysql-connector-python pandas

import mysql.connector

import pandas as pd
# 连接到数据库
cnx = mysql.connector.connect(
    user='username',
    password='password',
    host='localhost',
    database='database_name'
)

# 查询数据
query = "SELECT id, name FROM users WHERE age > 30"
data = pd.read_sql(query, cnx)

# 导出数据到 CSV 文件
data.to_csv('/path/to/users_age_above_30.csv', index=False)

# 关闭数据库连接
cnx.close()

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 查询数据
$sql = "SELECT id, name FROM users WHERE age > 30";
$result = $conn->query($sql);

// 打开文件写入
$file = fopen('/path/to/users_age_above_30.csv', 'w');

// 写入列头
fputcsv($file, ['id', 'name']);

// 写入数据
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        fputcsv($file, $row);
    }
} else {
    echo "0 结果";
}

// 关闭文件
fclose($file);

// 关闭连接
$conn->close();
?>

import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ExportData {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/database_name";
        String user = "username";
        String password = "password";
        
        String query = "SELECT id, name FROM users WHERE age > 30";
        String csvFile = "/path/to/users_age_above_30.csv";
        
        try (Connection con = DriverManager.getConnection(url, user, password);
             Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery(query);
             FileWriter fw = new FileWriter(csvFile)) {
             
            // 写入列头
            fw.append("id,name\n");
            
            // 写入数据
            while (rs.next()) {
                fw.append(rs.getString("id"))
                  .append(',')
                  .append(rs.getString("name"))
                  .append('\n');
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}