mysql用shell建100多字段表并导入

excel列超过160多个,导入时报错,把excel第一行另存为逗号分隔的csv文件,用shell建表

vim createTable.sh 

#!/bin/sh

str="CA6430M,HMC6432,HMC6433E,HMC6433EE3A,HMC6433A4M0,HMC6433C3M0,HMC6433C4M0,HMC6434A3M0,HMC6434A4M0,HMC6440A4T0,HMC6445B3,HMC6445B4,HMC6445E4M0,HMC6446B4M0,HMC6446C4M0,HMC6446D4M0,HMC6446E4M0,HMC6446F4M0,HMC7000EVM0,HMC7132A4H0,HMC7133C4H0,HMC7133A4H0,HMC7134B4S0,HMC7134C4S0,HMA7150DA4F,HMA7150DA4W,HMA7150DB4W,HMA7150DA4AW,HMA7150DB4BW,HMA7150DC4AW,HMA7150DC4F,HMC7151A4H0,HMC7151B4H0,HMC7151C4H0,HMC7151D4H0,HMC7152A4H0,HMC7152C4H0,HMC7154B4S0,HMC7154C4S0,HMC7160A3S0,HMC7160A4S0,HMC7160B3S0,HMC7160B3S1,HMC7160B4S0,HMC7160B4S1,HMC7160C3S0,HMC7161,HMC7161E3,HMC7161E3A,HMC7161E3B,HMC7161B,HMC7161BE3A,HMC7161BE3B,HMC7162A,HMC7162AE3,HMC7162AE3A,HMC7162AE3B,HMC7163,HMC7163E3A,HMC7163A3S1,HMC7163A4S0,HMC7164B3S0,HMC7164B4S0,HMC7164D4S0,HMC7164E4S0,HMC7165A4S0,HMC7165A4S1,HMC7165D4S1,HMC7165E4S1,HMC7165F3H0,HMC7165F4H0,HMC7165F4S0,HMC7165G4H0,HMC7165G4S0,HMC7165G4S1,HMC7165L4S1,HMC7165R4H0,HMC7165T4H0,HMC7166B3M0,HMC7166B4M0,HMC7166C4M0,HMC7166D4M0,HMC7166E4M0,HMC7180,HMC7185A,HMC7185A3,HMC7185A4,HMC7185A3H0,HMC7185A4H0,HMC7185B3,HMC7185B4,HMC7185B3H0,HMC7185B4H0,HMC7186A3,HMC7186A4,HMC7186B3,HMC7186B4,HMC7200A4T0,HMC7200A4T1,HMC7200A5T1,HMC7200B4T0,HMC7200B4T1,HMC7200B5T1,HMC7180B5T1,HMC7200B4S0,HMC7200B5S0,HMC7200C4S0,HMC7200C5S0,HMC7180C5S0,HMC7168E5S0,HMC7168F5S0,HMC7168D5S0,HMC7158B5S0,HMA7161GA4W,HMA7151GC4W,HMA7151GA4W,HMA7151GA4BW,HMA7151GC4BW,HMA7161GA4AW,HMA7152DA4W,HMA7152DB4W,HMA7152DA4BW,HMA7152DB4BW,HMA7160DA4W,HMA7160DA4CW,HMA7004S30BEV"
arr=(${str//,/ })

for i in ${arr[@]}
do
echo $i
mysql -uroot -p123456 -D testdb -e "alter table work_time_standard add column($i decimal(4,1))"
done

#>./createTable.sh 

SELECT VERSION();

SELECT table_name,table_comment FROM information_schema.tables WHERE table_schema = 'haima_dms_dev' AND table_comment LIKE '%检查单%'

SELECT COUNT(CODE),CODE,id FROM bas_car_model GROUP BY CODE,id
SELECT * FROM information_schema.`COLUMNS` WHERE table_schema = 'haima_dms_dev' AND column_comment LIKE '%保险%'

UPDATE
`testdb`.`p_host_part_consumable` AS ppc,`p_host_consumable` AS pc
SET

ppc.`consumable_id` = pc.`con_id`

WHERE
ppc.`con_sn` = pc.`con_sn`

UPDATE
`testdb`.`t_check_item`
SET
`type` = '4'
WHERE
`type_str` = 'D'
INSERT INTO `bas_car_model_setup`(`car_model_id`,`car_setup_id`) SELECT 1, id FROM `bas_car_setup`

posted @ 2015-12-07 11:33  facewater  阅读(1326)  评论(0编辑  收藏  举报