Code - Simple#!/bin/bash ( echo "BEGIN;" echo "USE database;" echo "INSERT INTO table (column1,column2) VALUES ('1','bob');" echo "INSERT INTO table (column1,column2) VALUES ('2','tom');" echo "INSERT INTO table (column1,column2) VALUES ('3','bill');" echo "DELETE FROM table WHERE column2='bob' LIMIT 2;" echo "COMMIT;" ) | mysql
File - loadme.sqlBEGIN; USE database; INSERT INTO table (column1,column2) VALUES ('1','bob'); INSERT INTO table (column1,column2) VALUES ('2','tom'); INSERT INTO table (column1,column2) VALUES ('3','bill'); DELETE FROM table WHERE column2='bob' LIMIT 2; COMMIT; Code#!/bin/bash cat loadme.sql | mysql
File - loadme.sqlINSERT INTO table (column1,column2) VALUES ('1','bob'); INSERT INTO table (column1,column2) VALUES ('2','tom'); INSERT INTO table (column1,column2) VALUES ('3','bill'); DELETE FROM table WHERE column2='bob' LIMIT 2; Code#!/bin/bash ( echo "BEGIN" cat loadme.sql echo "COMMIT" ) | mysql
File - loadme.csvid,first_name,last_name,email,country,ip_address 1,Michelle,George,mgeorge0@ebay.com,China,123.202.75.179 2,Sara,Evans,sevans1@hc360.com,Finland,134.70.196.70 3,Lawrence,Chavez,lchavez2@about.com,China,18.196.142.206 4,Roy,Carpenter,rcarpenter3@walmart.com,Albania,82.181.123.213 5,Shirley,Green,sgreen4@ucoz.ru,Libya,64.253.188.146 6,Adam,Wood,awood5@hhs.gov,Russia,221.26.34.134 7,Wayne,Stephens,wstephens6@nature.com,Russia,56.65.157.158 8,Theresa,Little,tlittle7@dmoz.org,Afghanistan,69.137.112.37 9,Angela,Sanchez,asanchez8@mayoclinic.com,Indonesia,105.61.90.239 10,Michael,Watson,mwatson9@nih.gov,China,252.233.46.123 11,Frank,Gonzalez,fgonzaleza@rambler.ru,Indonesia,93.237.180.19 12,Juan,Allen,jallenb@lycos.com,United States,218.237.224.77 13,Jack,Hall,jhallc@buzzfeed.com,Russia,4.72.91.28 14,Julie,Berry,jberryd@businesswire.com,China,217.110.87.152 15,Joshua,Simpson,jsimpsone@tinyurl.com,China,12.56.150.26 16,Mary,Hudson,mhudsonf@blogtalkradio.com,Tanzania,134.103.90.216 17,Johnny,Cunningham,jcunninghamg@ustream.tv,Malawi,240.198.146.11 18,Keith,Reynolds,kreynoldsh@huffingtonpost.com,Brazil,132.158.192.170 19,Tina,Bryant,tbryanti@so-net.ne.jp,Ukraine,241.208.105.145 20,David,Lawson,dlawsonj@vkontakte.ru,Finland,168.173.240.164 21,Mark,Nelson,mnelsonk@slate.com,China,184.186.226.254 22,Paul,Welch,pwelchl@businesswire.com,Sweden,13.152.202.44 23,Joyce,Jenkins,jjenkinsm@geocities.jp,Indonesia,8.103.77.179 24,Ernest,Morrison,emorrisonn@sakura.ne.jp,China,11.58.115.47 25,Brian,Bryant,bbryanto@imageshack.us,Poland,198.246.131.204 26,Carl,Fox,cfoxp@upenn.edu,Indonesia,1.89.229.49 27,Doris,Murray,dmurrayq@taobao.com,China,239.230.60.255 28,Doris,Bowman,dbowmanr@jiathis.com,Kazakhstan,71.87.138.11 29,Alan,Young,ayoungs@google.co.uk,Portugal,32.163.140.204 30,Evelyn,Montgomery,emontgomeryt@163.com,France,144.2.32.81 31,Alice,Daniels,adanielsu@quantcast.com,Ireland,247.130.69.39 32,Robert,Marshall,rmarshallv@fotki.com,Canada,116.30.129.9 33,Lois,Green,lgreenw@drupal.org,Indonesia,42.74.77.233 34,Arthur,Day,adayx@ucla.edu,North Korea,89.154.95.21 35,Patricia,Cox,pcoxy@ustream.tv,Turkmenistan,105.242.56.235 36,Antonio,Rice,aricez@jigsy.com,France,199.55.140.129 37,Billy,Simpson,bsimpson10@opensource.org,Mexico,209.115.2.51 38,Dorothy,Welch,dwelch11@list-manage.com,Ethiopia,198.175.147.200 39,Paul,Daniels,pdaniels12@ca.gov,Indonesia,227.236.178.238 40,Larry,Peters,lpeters13@vimeo.com,Jordan,226.138.52.216 41,Brandon,Williams,bwilliams14@skype.com,Russia,105.27.15.197 42,Elizabeth,Day,eday15@weebly.com,China,229.25.28.126 43,Ann,Henry,ahenry16@reference.com,China,42.97.38.8 44,Jimmy,Porter,jporter17@icq.com,France,223.26.225.195 45,Victor,Bowman,vbowman18@merriam-webster.com,Philippines,16.246.77.183 46,Brenda,Jackson,bjackson19@blog.com,Indonesia,128.43.154.62 47,Louis,Bryant,lbryant1a@mit.edu,Indonesia,95.22.26.179 48,Janet,Stephens,jstephens1b@usa.gov,Kazakhstan,231.160.86.102 49,Russell,Little,rlittle1c@joomla.org,Sweden,211.97.195.35 50,Keith,Ruiz,kruiz1d@sciencedaily.com,Israel,179.80.105.231 Code#!/bin/bash ( echo "BEGIN;" echo "USE database;" while IFS=',' read id first_name last_name email country ip_address; do echo "UPDATE table SET colum2='${email}' WHERE colum1='${id}';" done < loadme.csv echo "COMMIT;" ) | mysql
Code#!/bin/bash # using the < stops wc from printing the file name totalrows=$(wc -l < loadme.csv) ( echo "BEGIN;" echo "USE database;" echo "DELETE FROM table;" while IFS=',' read id first_name last_name email country ip_address; do echo "INSERT INTO table (colum1,colum2) VALUES ('${id}','${email}');" done < loadme.csv echo "IF ((SELECT COUNT(*) FROM table) = ${totalrows}) THEN" echo "COMMIT;" echo "ELSE" echo "ROLLBACK;" echo "END IF;" ) | mysql |
Scripting >
Bash & Mysql Transactions Example
posted Aug 14, 2015, 7:23 PM by Chris Franklin
|