mysql导入数据表数据类型,如何更改mysql中导入的电子表格的数据类型?

“逗号”可能会引起问题。是否为此使用加载数据填充?

假设表定义:

CREATE TABLE `t` (

`code` varchar(255) DEFAULT NULL,

`state` char(2) DEFAULT NULL,

`city` char(3) DEFAULT NULL,

`name` varchar(255) DEFAULT NULL,

`year` int(11) DEFAULT NULL,

`pop1` int(11) DEFAULT NULL,

`pop2` int(11) DEFAULT NULL,

`diff` int(11) DEFAULT NULL,

`ratio` float DEFAULT NULL

)

下面将导入文件:

load data local infile "test.txt"

into table t

columns terminated by '\t'

optionally enclosed by '"'

(code, state, city, name, year, @var1, @var2, diff, ratio)

set pop1=replace(@var1,",", ""),

pop2=replace(@var2, ",", "");

将插入以下行:

code: CN010010

state: 01

city: 001

name: Autauga County, AL

year: 2005

pop1: 23831

pop2: 23061

diff: 770

ratio: 3.2