linux下的存儲過程shell自動字段生成（懶人專用）

最近開發需要寫存儲過程，但是一個表的字段如果多的話，那就是苦力活，是以寫了這個存儲過程來自動生成表的字段、變量定義、變量賦初始值等，就讓博友們見識一下shell處理字元串的威力吧！

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

<code>db=$1</code>

<code>tab=$2</code>

<code>user=$3</code>

<code>passwd</code><code>=$4</code>

<code> </code> 

<code>db=zj_ihouse</code>

<code>tab=zj_broker_info</code>

<code>user=root</code>

<code>passwd</code><code>=</code><code>'abc@123'</code>

<code>mysql -u$user -p$</code><code>passwd</code> <code>-e </code><code>"desc $db.$tab"</code> <code>&gt; $db.$tab.tab</code>

<code>cat</code> <code>$db.$tab.tab | </code><code>grep</code> <code>-</code><code>v</code> <code>'Field'</code> <code>| </code><code>awk</code> <code>-F </code><code>' '</code> <code>'{print "declare v_"$1" "$2";"}'</code> <code>&gt; $db.$tab.txt</code>

<code>echo</code> <code>''</code> <code>&gt;&gt; $db.$tab.txt</code>

<code>cat</code> <code>$db.$tab.tab | </code><code>grep</code> <code>-</code><code>v</code> <code>'Field'</code> <code>| </code><code>grep</code> <code>-</code><code>v</code> <code>'Field'</code> <code>| </code><code>awk</code> <code>-F </code><code>' '</code> <code>'{print "v_"$1"="$2","}'</code> <code>| </code><code>sed</code> <code>"s/varchar(.*)/\'\'/g"</code> <code>| </code><code>sed</code> <code>"s/char(.*)/\'\'/g"</code> <code>| </code><code>sed</code> <code>"s/int(.*)/0/g"</code> <code>| </code><code>sed</code> <code>"s/datetime/\'0000-01-01 00:00:00\'/g"</code> <code>| </code><code>sed</code> <code>"s/mediumtext/\'\'/g"</code> <code>| </code><code>sed</code> <code>"s/double/0.0/g"</code> <code>| </code><code>tr</code> <code>-d </code><code>"\n"</code> <code>| </code><code>awk</code> <code>-F, '{</code><code>for</code><code>(i=1;i&lt;=NF;i++){</code><code>printf</code> <code>(i%5==0)?$i</code><code>",\n"</code><code>:$i</code><code>","</code><code>}}' | </code><code>sed</code> <code>"s/,,/,/g"</code> <code>&gt;&gt; $db.$tab.txt</code>

<code>cat</code> <code>$db.$tab.tab | </code><code>grep</code> <code>-</code><code>v</code> <code>'Field'</code> <code>| </code><code>awk</code> <code>-F </code><code>' '</code> <code>'{print "`"$1"`,"}'</code> <code>| </code><code>tr</code> <code>-d </code><code>"\n"</code> <code>| </code><code>awk</code> <code>-F, </code><code>'{for(i=1;i&lt;=NF;i++){printf (i%5==0)?$i",\n":$i","}}'</code> <code>| </code><code>sed</code> <code>"s/,,/,/g"</code> <code>&gt;&gt; $db.$tab.txt</code>

<code>cat</code> <code>$db.$tab.tab | </code><code>grep</code> <code>-</code><code>v</code> <code>'Field'</code> <code>| </code><code>awk</code> <code>-F </code><code>' '</code> <code>'{print "v_"$1","}'</code> <code>|  </code><code>tr</code> <code>-d </code><code>"\n"</code> <code>| </code><code>awk</code> <code>-F, </code><code>'{for(i=1;i&lt;=NF;i++){printf (i%5==0)?$i",\n":$i","}}'</code> <code>| </code><code>sed</code> <code>"s/,,/,/g"</code> <code>&gt;&gt; $db.$tab.txt</code>

<code>cat</code> <code>$db.$tab.tab | </code><code>grep</code> <code>-</code><code>v</code> <code>'Field'</code> <code>| </code><code>awk</code> <code>-F </code><code>' '</code> <code>'{print "`"$1"`=v_"$1","}'</code> <code>| </code><code>tr</code> <code>-d </code><code>"\n"</code> <code>| </code><code>awk</code> <code>-F, </code><code>'{for(i=1;i&lt;=NF;i++){printf (i%5==0)?$i",\n":$i","}}'</code> <code>| </code><code>sed</code> <code>"s/,,/,/g"</code> <code>&gt;&gt; $db.$tab.txt</code>

<code>rm</code> <code>-rf $db.$tab.tab</code>

生成的效果:

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

<code>declare</code> <code>v_id </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_create_date datetime;</code>

<code>declare</code> <code>v_del_flag </code><code>varchar</code><code>(1);</code>

<code>declare</code> <code>v_remarks </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_update_date datetime;</code>

<code>declare</code> <code>v_address </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_area_id </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_area_name </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_alias </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_area </code><code>double</code><code>;</code>

<code>declare</code> <code>v_borough_avgprice </code><code>double</code><code>;</code>

<code>declare</code> <code>v_borough_bank </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_bus </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_company </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_completion </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_content </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_costs </code><code>double</code><code>;</code>

<code>declare</code> <code>v_borough_developer </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_dining </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_green </code><code>double</code><code>;</code>

<code>declare</code> <code>v_borough_hospital </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_letter </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_name </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_number </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_parking </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_properties </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_shop </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_sight </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_support </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_totalarea </code><code>double</code><code>;</code>

<code>declare</code> <code>v_borough_type </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_borough_volume </code><code>double</code><code>;</code>

<code>declare</code> <code>v_check_advise </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_check_name </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_check_time </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_click_num </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_company_site </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_delete_name </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_delete_time </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_draw_thumb </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_draw_url </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_elementary_school </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_is_extension </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_is_new </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_is_promote </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_layout_map </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_middle_school </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_percent_change </code><code>double</code><code>;</code>

<code>declare</code> <code>v_pic_thumb </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_pic_url </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_plate_id </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_project_site </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_rent_num </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_room_type </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_sale_licence </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_sale_office </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_sel_phone </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_sell_num </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_sell_price </code><code>double</code><code>;</code>

<code>declare</code> <code>v_sell_time </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_status </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_submit_name </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_submit_time </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_video </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_create_by </code><code>varchar</code><code>(255);</code>

<code>declare</code> <code>v_update_by </code><code>varchar</code><code>(255);</code>

<code>v_id=</code><code>''</code><code>,v_create_date=</code><code>'0000-01-01 00:00:00'</code><code>,v_del_flag=</code><code>''</code><code>,v_remarks=</code><code>''</code><code>,v_update_date=</code><code>'0000-01-01 00:00:00'</code><code>,</code>

<code>v_address=</code><code>''</code><code>,v_area_id=</code><code>''</code><code>,v_area_name=</code><code>''</code><code>,v_borough_alias=</code><code>''</code><code>,v_borough_area=0.0,</code>

<code>v_borough_avgprice=0.0,v_borough_bank=</code><code>''</code><code>,v_borough_bus=</code><code>''</code><code>,v_borough_company=</code><code>''</code><code>,v_borough_completion=</code><code>''</code><code>,</code>

<code>v_borough_content=</code><code>''</code><code>,v_borough_costs=0.0,v_borough_developer=</code><code>''</code><code>,v_borough_dining=</code><code>''</code><code>,v_borough_green=0.0,</code>

<code>v_borough_hospital=</code><code>''</code><code>,v_borough_letter=</code><code>''</code><code>,v_borough_name=</code><code>''</code><code>,v_borough_number=</code><code>''</code><code>,v_borough_parking=</code><code>''</code><code>,</code>

<code>v_borough_properties=</code><code>''</code><code>,v_borough_shop=</code><code>''</code><code>,v_borough_sight=</code><code>''</code><code>,v_borough_support=</code><code>''</code><code>,v_borough_totalarea=0.0,</code>

<code>v_borough_type=</code><code>''</code><code>,v_borough_volume=0.0,v_check_advise=</code><code>''</code><code>,v_check_name=</code><code>''</code><code>,v_check_time=</code><code>''</code><code>,</code>

<code>v_click_num=</code><code>''</code><code>,v_company_site=</code><code>''</code><code>,v_delete_name=</code><code>''</code><code>,v_delete_time=</code><code>''</code><code>,v_draw_thumb=</code><code>''</code><code>,</code>

<code>v_draw_url=</code><code>''</code><code>,v_elementary_school=</code><code>''</code><code>,v_is_extension=</code><code>''</code><code>,v_is_new=</code><code>''</code><code>,v_is_promote=</code><code>''</code><code>,</code>

<code>v_layout_map=</code><code>''</code><code>,v_middle_school=</code><code>''</code><code>,v_percent_change=0.0,v_pic_thumb=</code><code>''</code><code>,v_pic_url=</code><code>''</code><code>,</code>

<code>v_plate_id=</code><code>''</code><code>,v_project_site=</code><code>''</code><code>,v_rent_num=</code><code>''</code><code>,v_room_type=</code><code>''</code><code>,v_sale_licence=</code><code>''</code><code>,</code>

<code>v_sale_office=</code><code>''</code><code>,v_sel_phone=</code><code>''</code><code>,v_sell_num=</code><code>''</code><code>,v_sell_price=0.0,v_sell_time=</code><code>''</code><code>,</code>

<code>v_status=</code><code>''</code><code>,v_submit_name=</code><code>''</code><code>,v_submit_time=</code><code>''</code><code>,v_video=</code><code>''</code><code>,v_create_by=</code><code>''</code><code>,</code>

<code>v_update_by=</code><code>''</code><code>,</code>

<code>`id`,`create_date`,`del_flag`,`remarks`,`update_date`,</code>

<code>`address`,`area_id`,`area_name`,`borough_alias`,`borough_area`,</code>

<code>`borough_avgprice`,`borough_bank`,`borough_bus`,`borough_company`,`borough_completion`,</code>

<code>`borough_content`,`borough_costs`,`borough_developer`,`borough_dining`,`borough_green`,</code>

<code>`borough_hospital`,`borough_letter`,`borough_name`,`borough_number`,`borough_parking`,</code>

<code>`borough_properties`,`borough_shop`,`borough_sight`,`borough_support`,`borough_totalarea`,</code>

<code>`borough_type`,`borough_volume`,`check_advise`,`check_name`,`check_time`,</code>

<code>`click_num`,`company_site`,`delete_name`,`delete_time`,`draw_thumb`,</code>

<code>`draw_url`,`elementary_school`,`is_extension`,`is_new`,`is_promote`,</code>

<code>`layout_map`,`middle_school`,`percent_change`,`pic_thumb`,`pic_url`,</code>

<code>`plate_id`,`project_site`,`rent_num`,`room_type`,`sale_licence`,</code>

<code>`sale_office`,`sel_phone`,`sell_num`,`sell_price`,`sell_time`,</code>

<code>`status`,`submit_name`,`submit_time`,`video`,`create_by`,</code>

<code>`update_by`,</code>

<code>v_id,v_create_date,v_del_flag,v_remarks,v_update_date,</code>

<code>v_address,v_area_id,v_area_name,v_borough_alias,v_borough_area,</code>

<code>v_borough_avgprice,v_borough_bank,v_borough_bus,v_borough_company,v_borough_completion,</code>

<code>v_borough_content,v_borough_costs,v_borough_developer,v_borough_dining,v_borough_green,</code>

<code>v_borough_hospital,v_borough_letter,v_borough_name,v_borough_number,v_borough_parking,</code>

<code>v_borough_properties,v_borough_shop,v_borough_sight,v_borough_support,v_borough_totalarea,</code>

<code>v_borough_type,v_borough_volume,v_check_advise,v_check_name,v_check_time,</code>

<code>v_click_num,v_company_site,v_delete_name,v_delete_time,v_draw_thumb,</code>

<code>v_draw_url,v_elementary_school,v_is_extension,v_is_new,v_is_promote,</code>

<code>v_layout_map,v_middle_school,v_percent_change,v_pic_thumb,v_pic_url,</code>

<code>v_plate_id,v_project_site,v_rent_num,v_room_type,v_sale_licence,</code>

<code>v_sale_office,v_sel_phone,v_sell_num,v_sell_price,v_sell_time,</code>

<code>v_status,v_submit_name,v_submit_time,v_video,v_create_by,</code>

<code>v_update_by,</code>

<code>`id`=v_id,`create_date`=v_create_date,`del_flag`=v_del_flag,`remarks`=v_remarks,`update_date`=v_update_date,</code>

<code>`address`=v_address,`area_id`=v_area_id,`area_name`=v_area_name,`borough_alias`=v_borough_alias,`borough_area`=v_borough_area,</code>

<code>`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,</code>

<code>`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,</code>

<code>`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,</code>

<code>`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,</code>

<code>`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,</code>

<code>`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,</code>

<code>`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,</code>

<code>`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,</code>

<code>`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,</code>

<code>`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,</code>

<code>`status`=v_status,`submit_name`=v_submit_name,`submit_time`=v_submit_time,`video`=v_video,`create_by`=v_create_by,</code>

<code>`update_by`=v_update_by,</code>

本文轉自 tanzhenchao 51CTO部落格，原文連結：http://blog.51cto.com/cmdschool/1695844，如需轉載請自行聯系原作者