問(wèn)題描述
我目前正在運(yùn)行兩個(gè)腳本.
I am currently running two scripts.
01_INIT_DATABASE.SQL
CREATE DATABASE foobar;
USE foobar;
CREATE USER 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT ALL PRIVILEGES ON foobar.* TO 'foo'@'localhost' WITH GRANT OPTION;
CREATE USER 'foo'@'%' IDENTIFIED BY 'bar';
GRANT ALL PRIVILEGES ON foobar.* TO 'foo'@'%' WITH GRANT OPTION;
和
01_DROP_DATABASE.SQL
USE foobar
DROP USER 'foo'@'localhost';
DROP USER 'foo'@'%';
DROP DATABASE IF EXISTS foobar;
如果一個(gè)或另一個(gè)運(yùn)行不正常，它們都會(huì)有選擇地失敗.
They each selectively fail if one or the other has not run properly.
我怎樣才能讓它們可靠地運(yùn)行(或者優(yōu)雅地失敗，例如，只刪除存在的用戶)
How can I get them to run reliably (or fail gracefully, for example, to only drop a user if it exists)
我正在通過(guò)一個(gè) ant 任務(wù)運(yùn)行它們(如果這會(huì)影響事情，但它實(shí)際上只是一個(gè) Java Exec)，形式為
I am running them through an ant task (if that affects things, but it is really just a Java Exec), in the form of
<exec executable="mysql" input="./src/main/ddl/create/01_init_database.sql">
<arg value="-uroot"/>
<arg value="-ptoor"/>
</exec>
我的 MySQL 版本是
My MySQL Version is
mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.2
更新IF EXISTS 和 IF NOT EXISTS 對(duì)用戶不起作用.它適用于 DATABASE.
Update IF EXISTS and IF NOT EXISTS does not work for USER. It works fine for DATABASE.
ERROR 1064 (42000):您的 SQL 語(yǔ)法有錯(cuò)誤；檢查與您的 MySQL 服務(wù)器版本相對(duì)應(yīng)的手冊(cè)，了解在第 1 行 'IF NOT EXISTS 'foo'@'localhost' IDENTIFIED BY 'bar'' 附近使用的正確語(yǔ)法
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS 'foo'@'localhost' IDENTIFIED BY 'bar'' at line 1
ERROR 1064 (42000):您的 SQL 語(yǔ)法有錯(cuò)誤；檢查與您的 MySQL 服務(wù)器版本相對(duì)應(yīng)的手冊(cè)，了解在第 1 行的 'IF EXISTS 'foo'@'%'' 附近使用的正確語(yǔ)法
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS 'foo'@'%'' at line 1
推薦答案
您可以將 IF NOT EXISTS 添加到您的數(shù)據(jù)庫(kù)架構(gòu)和用戶創(chuàng)建中:例如:
You can add IF NOT EXISTS to your databasechema and user creation: like:
CREATE DATABASE IF NOT EXISTS foobar;
CREATE USER IF NOT EXISTS 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT ALL PRIVILEGES ON foobar.* TO 'foo'@'localhost' WITH GRANT OPTION;
CREATE USER IF NOT EXISTS 'foo'@'%' IDENTIFIED BY 'bar';
GRANT ALL PRIVILEGES ON foobar.* TO 'foo'@'%' WITH GRANT OPTION;
以及掉落:
DROP USER IF EXISTS 'foo'@'localhost';
DROP USER IF EXISTS 'foo'@'%';
DROP DATABASE IF EXISTS foobar;
如下所述:如果用戶不存在，則僅適用于 mysql 5.7 及更高版本.不要使用5.7以下的create user語(yǔ)法，而是將grant語(yǔ)句改為:
As mentioned below: the user if not exists only works on mysql 5.7 and higher. Do not use the create user syntax below 5.7, but change the grant statement to:
GRANT ALL PRIVILEGES ON foobar.* TO 'foo'@'localhost' identified by 'password' WITH GRANT OPTION;
這篇關(guān)于如何在 MySQL 中可靠地刪除和創(chuàng)建數(shù)據(jù)庫(kù)和用戶的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！