Shell Perl 脚本查询 MySQL DB

Shell Perl 脚本查询 MySQL DB

tags: shell  perl db mysql unix select script

system:

**@***:~$ uname -a
Linux **-laptop 2.6.32-21-generic #32-Ubuntu SMP Fri Apr 16 08:09:38 UTC 2010 x86_64 GNU/Linux
**@***:~$

db:

**@***:~$ mysql --version
mysql  Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1

 mysql>use test;

mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   11 |
|  2 | b    |   12 |
+----+------+------+
2 rows in set (0.00 sec)
mysql>

bash shell:  db_select.sh

#!/bin/bash
HOST=127.0.0.1
USER=****
PASS=****

DATABASE=test
TABLE=user

QUERY=`mysql -h$HOST -u$USER -p$PASS << EOF
use $DATABASE;
select * from $TABLE where 1=1;
    exit
EOF`

echo $QUERY

Result:
**@***-laptop:/***/shell$  ./db_select.sh
id name age 1 a 11 2 b 12

perl shell:  db_select.pl

#!/usr/bin/perl
use strict;
my $HOST="127.0.0.1";
my $USER="******";
my $PASS="*****";

my $DB="test";
my $TABLES="user";

my $sql = "select * from $TABLES where 1=1";
my $result = `mysql -h$HOST -u$USER -p$PASS $DB -e"$sql"`;

print $result;
print "\n";

foreach my $line (split(/\n/, $result)){
    printf("%s\n",$line);
    for my $var (split(/\s/, $line))
    {
        print $var."\n";
    }
    print "\n";
}

Result:

**@***-laptop:/***/shell$ ./db_select.pl
id    name    age
1    a    11
2    b    12

id    name    age
id
name
age

1    a    11
1
a
11

2    b    12
2
b
12

--EOF--