MySQL SELECT INTO 用法詳解：建表複製、資料匯出與跨表插入

情境：將 student 表資料複製到新表 dust

方法一：建表同時複製（目標表不存在）

create table dust select * from student;

方法二：目標表已存在

insert into dust select * from student;

SELECT INTO 的限制與替代方案

MySQL 不支援 SELECT * INTO new_table FROM old_table（這是 SQL Server 語法）。

替代方案 1：CREATE TABLE ... SELECT

Create table new_table_name (Select * from old_table_name);

替代方案 2：mysqldump 匯出匯入

# 匯出
mysqldump -uroot -proot -h192.168.0.88 ok_db oktable2 > ok_db.sql
# 匯入
mysql> source d:/ok_db.sql

SELECT INTO OUTFILE / LOAD DATA INFILE

匯出資料到檔案

-- 全表匯出
Select * into outfile 'c://outfile.txt' from test;

-- 條件匯出
Select * into outfile 'c://outfile1.txt' from test where beginDate='2008-06-08';

從檔案匯入資料

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]

注意：FIELDS 和 LINES 子句必須寫在表名之後、欄位清單之前。

跨表複製避坑指南

• 目標表若有 AUTO_INCREMENT 主鍵，不可直接 INSERT INTO B SELECT * FROM A
• 正確寫法：明確指定欄位 INSERT INTO B (title) SELECT title FROM A