bash 如果不存在，则创建表挂起
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17599970/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create Table If Not Exists Hanging
提问by Tall Paul
I am still new to using databases, but currently I am using a mysql database and attempting to make my bash script unbreakable because it might be used in several different environments (dev, qa, etc). I know it will run correctly without this one line.
我仍然是使用数据库的新手，但目前我正在使用 mysql 数据库并试图使我的 bash 脚本牢不可破，因为它可能在几种不同的环境（dev、qa 等）中使用。我知道没有这一行它会正确运行。
CREATE TABLE IF NOT EXISTS backupfiles (fileName VARCHAR(20), archiveId VARCHAR(500), checkSum VARCHAR(100), glacierVault VARCHAR(100), timeStamp date);
I also know that if I put that exact line into my database when that table does not exist then it runs perfectly and if it does exist already it gives me a warning, but does not break.
我也知道，如果我在该表不存在时将该行放入我的数据库中，那么它会完美运行，如果它已经存在，它会给我一个警告，但不会中断。
+-------+------+------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------+
| Note | 1050 | Table 'backupfiles' already exists |
+-------+------+------------------------------------+
When I run the line above and there is no table named backupfiles it works perfectly, but when there is it hangs(when I say hangs it sits there and does nothing) 95% of the time and 5% of the time works. Has anyone run into a problem like this? I am using AWS RDS(Amazon Web serices Relational Database Service) and the mysql server is 5.5.27
当我运行上面的行并且没有名为 backupfiles 的表时，它可以完美运行，但是当它挂起时（当我说挂起时，它坐在那里什么也不做）95% 的时间和 5% 的时间工作。有没有人遇到过这样的问题？我正在使用 AWS RDS（Amazon Web 服务关系数据库服务）并且 mysql 服务器是 5.5.27
Here is all of my code that I relates to the mysql database
这是我与 mysql 数据库相关的所有代码
mysql -h portal-rds -u $user --password=$mysqlpw <<QUERY_INPUT
CREATE DATABASE IF NOT EXISTS $DATABASE;
use glacier;
CREATE TABLE IF NOT EXISTS backupfiles (fileName VARCHAR(20), archiveId VARCHAR(500), checkSum VARCHAR(100), glacierVault VARCHAR(100), timeStamp date);
INSERT INTO backupfiles VALUES ('$archive_file_name', '$archiveID', '$CURRENTVAULT', '$checkSum', CURDATE());
COMMIT;
QUERY_INPUT
采纳答案by Tall Paul
So far this is a working prototype, looking at improving it. For now if anyone else has this issue
到目前为止，这是一个工作原型，正在改进它。现在如果其他人有这个问题
echo "CREATE DATABASE IF NOT EXISTS $DATABASE;" > /tmp/runthis.sql
echo "use $DATABASE;" >> /tmp/runthis.sql
SQLSTMT="SELECT COUNT(1) FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE table_schema='${DATABASE}' AND table_name='backupfiles';"
TABLE_EXISTS=`mysql -h portal-rds -u $user --password=$mysqlpw -ANe"${SQLSTMT}"`
if [[ ${TABLE_EXISTS} -eq 0 ]]; then
SQLSTMT="CREATE TABLE backupfiles (fileName VARCHAR(20),"
SQLSTMT="${SQLSTMT} archiveId VARCHAR(500), checkSum VARCHAR(100),"
SQLSTMT="${SQLSTMT} glacierVault VARCHAR(100), timeStamp date);"
echo "${SQLSTMT}" >> /tmp/runthis.sql
fi
SQLSTMT="INSERT INTO backupfiles VALUES ('$archive_file_name',"
SQLSTMT="${SQLSTMT} '$archiveID', '$CURRENTVAULT', '$checkSum', CURDATE());"
echo "${SQLSTMT}" >> /tmp/runthis.sql
echo "COMMIT;" >> /tmp/runthis.sql
mysql -h portal-rds -u $user --password=$mysqlpw < /tmp/runthis.sql
