您可以将Excel表格
另存为CSV文件,然后使用
LOAD DATA INFILE命令将此类文件导入具有与Excel表格相同列的临时MySQL表中,并最终将临时表记录拆分成三个表“cities”、“towns”和“districts”。前提条件是:由于Excel文件中没有出现任何“id”字段,因此我假设所有的id都是“auto_increment”字段;此外,“towns”表中的“continent”字段将始终设置为空值,因为此字段在您的Excel表格中不存在;最后,我将假设所有的“name”字段的最大长度为255个字符。从您的示例数据开始,通过将Excel表格导出为CSV格式并保存(例如)到“
C:\Temp\excel.csv”文件中,您应该会得到以下内容:
"city name","town name","district name"
"X","Y","A"
"X","Y","B"
"X","K","C"
"X","K","D"
要将此文件导入到您的MySQL数据库中,请创建一个名为"
excel2mysql.sql"的文件,其内容如下,并
执行它:
DROP TABLE IF EXISTS excel_table;
CREATE temporary TABLE excel_table (
city_name VARCHAR(255),
town_name VARCHAR(255),
district_name VARCHAR(255)
) DEFAULT CHARSET utf8;
LOAD DATA LOCAL INFILE 'C:/Temp/excel.csv'
INTO TABLE excel_table
CHARACTER SET utf8
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
city_id int NOT NULL auto_increment,
city_name VARCHAR(255),
primary key (city_id)
) DEFAULT CHARSET utf8;
INSERT INTO cities
SELECT distinctrow NULL, city_name
FROM excel_table
ORDER BY city_name;
DROP TABLE IF EXISTS towns;
CREATE TABLE towns (
town_id int NOT NULL auto_increment,
city_id int NOT NULL,
town_name VARCHAR(255),
continent VARCHAR(255),
primary key (town_id)
) DEFAULT CHARSET utf8;
INSERT INTO towns
SELECT distinctrow NULL, city_id, town_name, ''
FROM excel_table, cities
WHERE cities.city_name=excel_table.city_name
ORDER BY town_name;
DROP TABLE IF EXISTS districts;
CREATE TABLE districts (
district_id int NOT NULL auto_increment,
town_id int NOT NULL,
district_name VARCHAR(255),
primary key (district_id)
) DEFAULT CHARSET utf8;
INSERT INTO districts
SELECT distinctrow NULL, town_id, district_name
FROM excel_table, towns
WHERE towns.town_name=excel_table.town_name
ORDER BY district_name;