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:

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

The following results are returned after execution:

Manipulating MySQL in shell scripts

Exporting data

MYSQL="mysql -h192.168.1.102 -uroot -p123456 --default-character-set=utf8 -A -N"#这里面有两个参数，-A、-N，-A的含义是不去预读全部数据表信息，这样可以解决在数据表很多的时候卡死的问题#-N，很简单，Don't write column names in results，获取的数据信息省去列名称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的时候一定要加上双引号，不让导出的数据会挤在一行#下面是返回的测试数据3       吴彦祖  325       王力宏  326       ab      327       黄晓明  338       anonymous       32

Inserting data

#先看看要导入的数据格式，三列，分别是id，名字，年龄（数据是随便捏造的），放入data.user.txt12 tf 2313 米勒 2414 西安电子科技大学 9015 西安交大 9016 北京大学 90#OLF_IFS=$IFS#IFS=","#临时设置默认分隔符为逗号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 results

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

Update data

#先看看更新数据的格式，将左边一列替换为右边一列，只有左边一列的删除，下面数据放入update.user.txttf twoFile西安电子科技大学 西军电西安交大 西安交通大学北京大学cat update.user.txt | while read src dstdoif [ ! -z "${src}" -a ! -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 | 米勒                   |  24 || 14 | 西军电 |  90 || 15 | 西安交通大学         |  90 |+----+--------------------------+-----+

This copyright belongs to author iforever[ luluyrt at 163 dot com ] all, without the author's consent to prohibit any form of reprint, reprinted article must be in the article page obvious location to the author and the original text connection.

The above describes the shell processing MySQL Add, delete, change, check, including the aspects of the content, I hope that the PHP tutorial interested in a friend helpful.

