最近开发需要写存储过程，但是一个表的字段如果多的话，那就是苦力活，所以写了这个存储过程来自动生成表的字段、变量定义、变量赋初始值等，就让博友们见识一下shell处理字符串的威力吧！
如果你是DBA你看到我这个脚本一定会喜欢
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
db=$1
tab=$2
user=$3
passwd
=$4
db=zj_ihouse
tab=zj_broker_info
user=root
passwd
=
'abc@123'
mysql -u$user -p$
passwd
-e
"desc $db.$tab"
> $db.$tab.tab
cat
$db.$tab.tab |
grep
-
v
'Field'
|
awk
-F
' '
'{print "declare v_"$1" "$2";"}'
> $db.$tab.txt
echo
''
>> $db.$tab.txt
echo
''
>> $db.$tab.txt
cat
$db.$tab.tab |
grep
-
v
'Field'
|
grep
-
v
'Field'
|
awk
-F
' '
'{print "v_"$1"="$2","}'
|
sed
"s/varchar(.*)/\'\'/g"
|
sed
"s/char(.*)/\'\'/g"
|
sed
"s/int(.*)/0/g"
|
sed
"s/datetime/\'0000-01-01 00:00:00\'/g"
|
sed
"s/mediumtext/\'\'/g"
|
sed
"s/double/0.0/g"
|
tr
-d
"\n"
|
awk
-F, '{
for
(i=1;i<=NF;i++){
printf
(i%5==0)?$i
",\n"
:$i
","
}}' |
sed
"s/,,/,/g"
>> $db.$tab.txt
echo
''
>> $db.$tab.txt
echo
''
>> $db.$tab.txt
cat
$db.$tab.tab |
grep
-
v
'Field'
|
awk
-F
' '
'{print "`"$1"`,"}'
|
tr
-d
"\n"
|
awk
-F,
'{for(i=1;i<=NF;i++){printf (i%5==0)?$i",\n":$i","}}'
|
sed
"s/,,/,/g"
>> $db.$tab.txt
echo
''
>> $db.$tab.txt
echo
''
>> $db.$tab.txt
cat
$db.$tab.tab |
grep
-
v
'Field'
|
awk
-F
' '
'{print "v_"$1","}'
|
tr
-d
"\n"
|
awk
-F,
'{for(i=1;i<=NF;i++){printf (i%5==0)?$i",\n":$i","}}'
|
sed
"s/,,/,/g"
>> $db.$tab.txt
echo
''
>> $db.$tab.txt
echo
''
>> $db.$tab.txt
cat
$db.$tab.tab |
grep
-
v
'Field'
|
awk
-F
' '
'{print "`"$1"`=v_"$1","}'
|
tr
-d
"\n"
|
awk
-F,
'{for(i=1;i<=NF;i++){printf (i%5==0)?$i",\n":$i","}}'
|
sed
"s/,,/,/g"
>> $db.$tab.txt
rm
-rf $db.$tab.tab
|
生成的效果:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
|
declare
v_id
varchar
(255);
declare
v_create_date datetime;
declare
v_del_flag
varchar
(1);
declare
v_remarks
varchar
(255);
declare
v_update_date datetime;
declare
v_address
varchar
(255);
declare
v_area_id
varchar
(255);
declare
v_area_name
varchar
(255);
declare
v_borough_alias
varchar
(255);
declare
v_borough_area
double
;
declare
v_borough_avgprice
double
;
declare
v_borough_bank
varchar
(255);
declare
v_borough_bus
varchar
(255);
declare
v_borough_company
varchar
(255);
declare
v_borough_completion
varchar
(255);
declare
v_borough_content
varchar
(255);
declare
v_borough_costs
double
;
declare
v_borough_developer
varchar
(255);
declare
v_borough_dining
varchar
(255);
declare
v_borough_green
double
;
declare
v_borough_hospital
varchar
(255);
declare
v_borough_letter
varchar
(255);
declare
v_borough_name
varchar
(255);
declare
v_borough_number
varchar
(255);
declare
v_borough_parking
varchar
(255);
declare
v_borough_properties
varchar
(255);
declare
v_borough_shop
varchar
(255);
declare
v_borough_sight
varchar
(255);
declare
v_borough_support
varchar
(255);
declare
v_borough_totalarea
double
;
declare
v_borough_type
varchar
(255);
declare
v_borough_volume
double
;
declare
v_check_advise
varchar
(255);
declare
v_check_name
varchar
(255);
declare
v_check_time
varchar
(255);
declare
v_click_num
varchar
(255);
declare
v_company_site
varchar
(255);
declare
v_delete_name
varchar
(255);
declare
v_delete_time
varchar
(255);
declare
v_draw_thumb
varchar
(255);
declare
v_draw_url
varchar
(255);
declare
v_elementary_school
varchar
(255);
declare
v_is_extension
varchar
(255);
declare
v_is_new
varchar
(255);
declare
v_is_promote
varchar
(255);
declare
v_layout_map
varchar
(255);
declare
v_middle_school
varchar
(255);
declare
v_percent_change
double
;
declare
v_pic_thumb
varchar
(255);
declare
v_pic_url
varchar
(255);
declare
v_plate_id
varchar
(255);
declare
v_project_site
varchar
(255);
declare
v_rent_num
varchar
(255);
declare
v_room_type
varchar
(255);
declare
v_sale_licence
varchar
(255);
declare
v_sale_office
varchar
(255);
declare
v_sel_phone
varchar
(255);
declare
v_sell_num
varchar
(255);
declare
v_sell_price
double
;
declare
v_sell_time
varchar
(255);
declare
v_status
varchar
(255);
declare
v_submit_name
varchar
(255);
declare
v_submit_time
varchar
(255);
declare
v_video
varchar
(255);
declare
v_create_by
varchar
(255);
declare
v_update_by
varchar
(255);
v_id=
''
,v_create_date=
'0000-01-01 00:00:00'
,v_del_flag=
''
,v_remarks=
''
,v_update_date=
'0000-01-01 00:00:00'
,
v_address=
''
,v_area_id=
''
,v_area_name=
''
,v_borough_alias=
''
,v_borough_area=0.0,
v_borough_avgprice=0.0,v_borough_bank=
''
,v_borough_bus=
''
,v_borough_company=
''
,v_borough_completion=
''
,
v_borough_content=
''
,v_borough_costs=0.0,v_borough_developer=
''
,v_borough_dining=
''
,v_borough_green=0.0,
v_borough_hospital=
''
,v_borough_letter=
''
,v_borough_name=
''
,v_borough_number=
''
,v_borough_parking=
''
,
v_borough_properties=
''
,v_borough_shop=
''
,v_borough_sight=
''
,v_borough_support=
''
,v_borough_totalarea=0.0,
v_borough_type=
''
,v_borough_volume=0.0,v_check_advise=
''
,v_check_name=
''
,v_check_time=
''
,
v_click_num=
''
,v_company_site=
''
,v_delete_name=
''
,v_delete_time=
''
,v_draw_thumb=
''
,
v_draw_url=
''
,v_elementary_school=
''
,v_is_extension=
''
,v_is_new=
''
,v_is_promote=
''
,
v_layout_map=
''
,v_middle_school=
''
,v_percent_change=0.0,v_pic_thumb=
''
,v_pic_url=
''
,
v_plate_id=
''
,v_project_site=
''
,v_rent_num=
''
,v_room_type=
''
,v_sale_licence=
''
,
v_sale_office=
''
,v_sel_phone=
''
,v_sell_num=
''
,v_sell_price=0.0,v_sell_time=
''
,
v_status=
''
,v_submit_name=
''
,v_submit_time=
''
,v_video=
''
,v_create_by=
''
,
v_update_by=
''
,
`id`,`create_date`,`del_flag`,`remarks`,`update_date`,
`address`,`area_id`,`area_name`,`borough_alias`,`borough_area`,
`borough_avgprice`,`borough_bank`,`borough_bus`,`borough_company`,`borough_completion`,
`borough_content`,`borough_costs`,`borough_developer`,`borough_dining`,`borough_green`,
`borough_hospital`,`borough_letter`,`borough_name`,`borough_number`,`borough_parking`,
`borough_properties`,`borough_shop`,`borough_sight`,`borough_support`,`borough_totalarea`,
`borough_type`,`borough_volume`,`check_advise`,`check_name`,`check_time`,
`click_num`,`company_site`,`delete_name`,`delete_time`,`draw_thumb`,
`draw_url`,`elementary_school`,`is_extension`,`is_new`,`is_promote`,
`layout_map`,`middle_school`,`percent_change`,`pic_thumb`,`pic_url`,
`plate_id`,`project_site`,`rent_num`,`room_type`,`sale_licence`,
`sale_office`,`sel_phone`,`sell_num`,`sell_price`,`sell_time`,
`status`,`submit_name`,`submit_time`,`video`,`create_by`,
`update_by`,
v_id,v_create_date,v_del_flag,v_remarks,v_update_date,
v_address,v_area_id,v_area_name,v_borough_alias,v_borough_area,
v_borough_avgprice,v_borough_bank,v_borough_bus,v_borough_company,v_borough_completion,
v_borough_content,v_borough_costs,v_borough_developer,v_borough_dining,v_borough_green,
v_borough_hospital,v_borough_letter,v_borough_name,v_borough_number,v_borough_parking,
v_borough_properties,v_borough_shop,v_borough_sight,v_borough_support,v_borough_totalarea,
v_borough_type,v_borough_volume,v_check_advise,v_check_name,v_check_time,
v_click_num,v_company_site,v_delete_name,v_delete_time,v_draw_thumb,
v_draw_url,v_elementary_school,v_is_extension,v_is_new,v_is_promote,
v_layout_map,v_middle_school,v_percent_change,v_pic_thumb,v_pic_url,
v_plate_id,v_project_site,v_rent_num,v_room_type,v_sale_licence,
v_sale_office,v_sel_phone,v_sell_num,v_sell_price,v_sell_time,
v_status,v_submit_name,v_submit_time,v_video,v_create_by,
v_update_by,
`id`=v_id,`create_date`=v_create_date,`del_flag`=v_del_flag,`remarks`=v_remarks,`update_date`=v_update_date,
`address`=v_address,`area_id`=v_area_id,`area_name`=v_area_name,`borough_alias`=v_borough_alias,`borough_area`=v_borough_area,
`borough_avgprice`=v_borough_avgprice,`borough_bank`=v_borough_bank,`borough_bus`=v_borough_bus,`borough_company`=v_borough_company,`borough_completion`=v_borough_completion,
`borough_content`=v_borough_content,`borough_costs`=v_borough_costs,`borough_developer`=v_borough_developer,`borough_dining`=v_borough_dining,`borough_green`=v_borough_green,
`borough_hospital`=v_borough_hospital,`borough_letter`=v_borough_letter,`borough_name`=v_borough_name,`borough_number`=v_borough_number,`borough_parking`=v_borough_parking,
`borough_properties`=v_borough_properties,`borough_shop`=v_borough_shop,`borough_sight`=v_borough_sight,`borough_support`=v_borough_support,`borough_totalarea`=v_borough_totalarea,
`borough_type`=v_borough_type,`borough_volume`=v_borough_volume,`check_advise`=v_check_advise,`check_name`=v_check_name,`check_time`=v_check_time,
`click_num`=v_click_num,`company_site`=v_company_site,`delete_name`=v_delete_name,`delete_time`=v_delete_time,`draw_thumb`=v_draw_thumb,
`draw_url`=v_draw_url,`elementary_school`=v_elementary_school,`is_extension`=v_is_extension,`is_new`=v_is_new,`is_promote`=v_is_promote,
`layout_map`=v_layout_map,`middle_school`=v_middle_school,`percent_change`=v_percent_change,`pic_thumb`=v_pic_thumb,`pic_url`=v_pic_url,
`plate_id`=v_plate_id,`project_site`=v_project_site,`rent_num`=v_rent_num,`room_type`=v_room_type,`sale_licence`=v_sale_licence,
`sale_office`=v_sale_office,`sel_phone`=v_sel_phone,`sell_num`=v_sell_num,`sell_price`=v_sell_price,`sell_time`=v_sell_time,
`status`=v_status,`submit_name`=v_submit_name,`submit_time`=v_submit_time,`video`=v_video,`create_by`=v_create_by,
`update_by`=v_update_by,
|
本文转自 tanzhenchao 51CTO博客，原文链接：http://blog.51cto.com/cmdschool/1695844，如需转载请自行联系原作者
