Creating MySQL database with variables

Question

I want to do something along the lines of

CREATE DATABASE IF NOT EXISTS @database_name;

From reading the mysql syntax for prepared statements it appears that they cant be used to create databases, otherwise something like this would have been ok.

SET @s = CONCAT('CREATE DATABASE IF NOT EXISTS ',@database_name);
PREPARE stmt FROM @s;
EXECUTE stmt;

Ideally I want it as something I can run from a .sql file from a shell script

#!/bin/bash

MYSQL="mysql"
`${MYSQL} --version >& /dev/null`
if [ $? != 0 ]; then
    MYSQL="mysql5"
    `${MYSQL} --version > /dev/null`
    if [ $? != 0 ]; then
        echo "Can't find mysql binary?"
        exit 1
    fi
fi

 ${MYSQL} -u root --password=###### -e "set @database_name:='ben_search';source CreateSkeleton.sql;"

Any ideas?

My idea is to put placeholder in your .sql file and use shell to replace them with your database name before passing the file to mysql. — Mchl
– Mchl, Commented Sep 19, 2011 at 9:37
Seems similar to this answer, although that is with a procedure. I tried your prepared statement with MySQL 5.5.19, and it appeared to work. Are you getting an error with the prepared statement? — John McCarthy
– John McCarthy, Commented Aug 14, 2013 at 15:19
Ah, I looked at the documentation link a bit closer. Looks like MySQL 5.5 supports a number of statements that weren't supported in 5.0. So, to be clear, MySQL 5.5 will support prepared statements with CREATE DATABASE. — John McCarthy
– John McCarthy, Commented Aug 14, 2013 at 15:27

RandomSeed · Accepted Answer · 2014-02-27 12:40:06Z

2

You could just take the CREATE DATABASE statement away from your SQL file and issue it from your shell script:

#!/bin/bash
dbname='my_db'
mysql -e "CREATE DATABASE $dbname"
mysql $dbname < /path/to/file/CreateSkeleton.sql

answered Feb 27, 2014 at 12:40

RandomSeed

29.9k6 gold badges54 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RandomSeed Over a year ago

I know this is an old question and I hope you found a solution by then... I'm just trying to relieve my compulsive need for closure.

Collectives™ on Stack Overflow

Creating MySQL database with variables

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related