Shell processes mysql addition, deletion, modification, and query

: This article mainly introduces how to use shell to process mysql additions, deletions, modifications, and queries. For more information about PHP tutorials, see. Introduction

In the past few days, I made a task to compare the data in the two data tables. I wrote a version in PHP yesterday, but considering that some machines do not have php or php does not compile mysql extensions, the mysql series functions cannot be used, and the script is invalid. write a shell version today, so that all linux series machines can run.

How does shell operate mysql?

Shell mysql uses mysql commands to execute statements through parameters, which is the same as that in other programs. let's take a look at the following parameters:

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

Therefore, we can execute the statement through mysql-e, as shown below:

mysql -hlocalhost -P3306 -uroot -p123456 $test --default-character-set=utf8 -e "select * from users"

The following result is returned after execution:

Operate mysql in shell script

Export data

MYSQL = "mysql-h192.168.1.102-uroot-p123456 -- default-character-set = utf8-A-N" # There are two parameters,-A,-N, -A means not to read all data table information in advance, which can solve the problem of freezing many data tables #-N, which is very simple. Don't write column names in results, the retrieved data is omitted from the column name SQL = "select * from test. user "result =" $ ($ MYSQL-e "$ SQL") "dump_data =. /data.user.txt> $ dump_dataecho-e "$ result"> $ dump_data # note that the echo-e "$ result"> $ dump_data must be enclosed by double quotation marks, do not squeeze the exported data into one line # The following is the returned test data 3 Wu Yanzu 325 Wang Li Hong 326 AB 327 Huang Xiaoming 338 anonymous 32

Insert data

201712 tf 2313 Miller 2414 Xi'an University of Electronic Science and Technology 9015 Xi'an Jiaotong University 9016 Peking University 90 # OLF_IFS = $ IFS # IFS = ", "# The default delimiter for temporary settings is cat data.user.txt | while read id name agedosql =" insert into test. user (id, name, age) values ($ {id}, '$ {name}', $ {age}); "$ MYSQL-e" $ SQL "done

Output result

+ ---- + ------------------------ + ----- + | Id | name | age | + ---- + -------------------------- + ----- + | 12 | tf | 23 | 13 | Miller | 24 | 14 | Xi'an Electronics university of Science and Technology | 90 | 15 | Xi'an Jiaotong University | 90 | 16 | Peking University | 90 | + ---- + ------------------------ + ----- +

Update data

Tf twoFile Xi 'an University of Electronic Science and Technology Xi 'an Jiaotong university Xi'an Jiaotong University Peking University cat update.user.txt | while read src dstdoif [! -Z "$ {src}"-! -Z "$ {dst}"] thensql = "update test. user set name = '$ {dst}' where name = '$ {src}'" fiif [! -Z "$ {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 | Miller | 24 | 14 | Xi Jun electric power | 90 | 15 | Xi'an Jiaotong University | 90 | + ---- + ---------------------------- + ----- +

This article is copyrighted by iforever [ Luluyrt at 163 dot com ] All, reprinting in any form is prohibited without the author's consent. after reprinting, the author must be clearly connected to the original article on the article page.

The above section describes how to use shell to process mysql additions, deletions, modifications, and queries, including some content. I hope my friends who are interested in PHP tutorials will be helpful.