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.

Click to copy

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

Click to copy

#!/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?

Answer 1

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

Click to copy

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