Shell processing MySQL Add, delete, change, check

Introduction

These days to do a task, compared to two data tables in the data, yesterday wrote a version of PHP, but considering that some machines do not have PHP or PHP does not compile MySQL extension, you can not use the MySQL series of functions, the script is invalid, write a shell version today, so, It can be run on all Linux series machines.

How does the shell operate MySQL?

The shell operation of MySQL is actually through the MySQL command through the parameters to execute the statement, and other programs inside the same, look at the following parameters:

-e, --execute=name  Execute command and quit. (Disables --force and history file.)

So we can execute the statement through MYSQL-E, just like this:

$test --default-character-set=utf8 -e "select * from users"

The following results are returned after execution:

Manipulating MySQL export data in shell scripts

Mysql="Mysql-h192.168.1.102-uroot-p123456--default-character-set=utf8-a-N" #这里面有两个参数,-A,-n,-a the meaning is not to read all the data table information, so as to solve the data table in a lot of time stuck to the problem #- N, very simple, Don ' t write column names in results, get the data information omitted column name Sql= "select * from Test.user" result= "$ ($MYSQL-E" $sql ")" Dump_data=. /data.user.txt> $dump _dataecho-e  "$result" > $dump _data #这里要额外注意, Echo-e "$result" > $dump _data must be enclosed in double quotes, so that the exported data will be squeezed in one line  #下面是返回的测试数据 3 Daniel Wu 325 leehom 326 ab 327 Huang Xiaoming 33< Span class= "Hljs-number" >8 anonymous 32       

Inserting data

#先看看要导入的数据格式, three columns, respectively ID, name, age (data is casually fabricated), put into data.user.txttf2313 Miller 2414 Xidian University 9015 Xi ' an Jiaotong University 9016 Span class= "Hljs-number" >90 #OLF_IFS = $IFS  #IFS = ","  #临时设置默认分隔符为逗号cat data.user.txt | while read ID name agedosql=< Span class= "hljs-string" > "INSERT into Test.user (ID, name, age) VALUES (${id}, ' ${name} ', ${age}); "  $MYSQL -e  $sql "done         

Output results

+----+--------------------------+-----+| id | name                     | age |+----+--------------------------+-----+| 12 | tf                       |  23 || 13 | 米勒                   |  24 || 14 | 西安电子科技大学 |  90 || 15 | 西安交大             |  90 || 16 | 北京大学             |  90 |+----+--------------------------+-----+

Update data

#先看看更新数据的格式, the left column is replaced by the right column, only the left column is deleted, and the following data is put into Update.user.txttf Twofile Xidian University West Army Electric Xian Jiaotong XI ' an XI ' an Jiaotong univ. cat Update.user.txt |WhileRead SRC DSTDoif [!-Z"${SRC} "-a!-Z  "${dst}"]thensql=" update test.user set name= ' ${DST} ' where name= ' ${src} ' "fiif [!-Z] Span class= "hljs-string" > "${src}" -a-z " ${dst} "]thensql=  "delete from Test.user where name= ' ${src} '" fi $MYSQL -e  $sql "done        

Output Result:

+----+--------------------------+-----+| id | name                     | age |+----+--------------------------+-----+| 12 | twoFile                  |  23 || 13 | 米勒                   |  24 || 14 | 西军电 |  90 || 15 | 西安交通大学         |  90 |+----+--------------------------+-----+

This article copyright belongs to the author iforever[] all, without the author's consent to prohibit any form of reprint, reproduced article must be in the article page obvious location to the author and the original link.

This article transferred from: http://www.cnblogs.com/iforever/p/4459857.html

Shell processing MySQL Add, delete, change, check