shell脚本获取mysql插入数据自增长id的值

shell脚本获取mysql插入数据自增长id的值

在shell脚本中我们可以通过last_insert_id()获取id值，但是，需要注意的是，该函数必须在执行插入操作的sql语句之后，立即调用，否则获取的值就为0，LAST_INSERT_ID 是与table无关的，如果向表a插入数据后，在向表b插入数据，LAST_INSERT_ID会改变。当然还有其他方法:

1. select max(id) from tablename；
2. select @@IDENTITY;
3. SHOW TABLE STATUS;

具体的差别和各自具体的特征，本文不再叙述，自行百度即可.

本文主要以LAST_INSERT_ID()为例：

错误代码示例如下:

echo
$($MYSQL -e "INSERT INTO problem_logger VALUES (null,$REPORT_DATE,$FIXED_DATE,'$PROB_SYMPTOMS','$PROB_SOLUTIONS');")
id=$($MYSQL -e "SELECT LAST_INSERT_ID() id")
id=`echo $id | gawk '{print $2}'`
$MYSQL <<EOF
SELECT * FROM problem_logger where id_number=$id\G
EOF

上述代码获取到的id值就为0，而不是我们期望的值

正确的代码如下:

echo
$($MYSQL -e "INSERT INTO problem_logger VALUES (null,$REPORT_DATE,$FIXED_DATE,'$PROB_SYMPTOMS','$PROB_SOLUTIONS');")
    id=$($MYSQL -e "SELECT LAST_INSERT_ID() id")
id=`echo $id | gawk '{print $2}'`
$MYSQL <<EOF
SELECT * FROM problem_logger where id_number=$id\G
EOF

完整的访问数据库，插入数据，并获取自增id值的shell脚本如下:

备注:1. 数据库名称:Problem_Trek      操作的表名称:problem_logger

#!/bin/bash
#
# Record_Problem - records system problems in database
#
###########################################################
#
# Determine mysql location & put into variable
#
MYSQL=`which mysql`" Problem_Trek -u root"
#
###########################################################
#
# Create Record Id & Report_Date
#
#ID_NUMBER=`date +%y%m%d%H%M`
#
REPORT_DATE=`date +%y%m%d`
#
############################################################
#
# Acquire information to put into table
#
echo
echo -e "Birefly describe the problem & its symptoms: \c"
#
read ANSWER
PROB_SYMPTOMS=$ANSWER
#
# Set Fixed Date & Problem Solution to null for now
#
FIXED_DATE=0
PROB_SOLUTIONS=""
#
#############################################################
#
# Insert acquired information into table
#
echo 
echo "Problem recorded as follows:"
echo
id=$($MYSQL -e "INSERT INTO problem_logger VALUES (null,$REPORT_DATE,$FIXED_DATE,'$PROB_SYMPTOMS','$PROB_SOLUTIONS');SELECT LAST_INSERT_ID() id")
id=`echo $id | gawk '{print $2}'`
$MYSQL <<EOF
SELECT * FROM problem_logger where id_number=$id\G
EOF
#
#############################################################
#
# Check if want to enter a solution now
#
echo 
echo -e "Do you have a solution yet?(y/n) \c"
read ANSWER
#
case $ANSWER in
y|Y|YES|yes|Yes|yEs|yeS|YEs|yES)
    ./Update_Problem.sh $id
#
;;
*)
# if answer is anything but yes, just exit script
;;
esac
#
############################################################