如何在MySQL中可靠地删除并创建数据库和用户 [英] How Can I Reliably Drop and Create a Database and a User in MySQL
问题描述
我当前正在运行两个脚本.
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;
如果一个或另一个运行不正常,它们将有选择地失败.
They each selectively fail if one or the other has not run properly.
如何使它们可靠运行(或优雅地失败,例如仅在存在用户的情况下将其删除)
How can I get them to run reliably (or fail gracefully, for example, to only drop a user if it exists)
我正在通过ant任务(如果会影响事物,但实际上只是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版本是
mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.2
更新IF EXISTS和IF NOT EXISTS对USER不起作用.它适用于DATABASE.
Update IF EXISTS and IF NOT EXISTS does not work for USER. It works fine for DATABASE.
错误1064(42000):您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册以获取正确的语法,以在第1行的'IF NOT EXISTS'foo'@'localhost'IDENTIFIED BY'bar'附近使用
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
错误1064(42000):您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册以获取正确的语法,以在第1行的'IF EXISTS'foo'@'%''附近使用
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添加到数据库架构和用户创建中:
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语法,而是将grant语句更改为:
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;
这篇关于如何在MySQL中可靠地删除并创建数据库和用户的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!