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
#
############################################################