1. Show databases;

When I want to create a new database it will suggest: tenant_5 2.Next database name suggestion tenant_5

Is there any way to do like this ?

I have not found any doc.

CodePudding user response：

You can get the list of existing user databases in your server with

SELECT SCHEMA_NAME
  FROM information_schema.SCHEMATA
 WHERE SCHEMA_NAME NOT IN 
    ('mysql', 'information_schema', 'performance_schema', 'sys')
 ORDER BY SCHEMA_NAME;

Once you know the names of the databases already present, you can come up with another one using whatever algorithm you like.

But, consider this: what if your app proves successful and you find yourself with 500 new customers a day? In that case you will be really sorry if you have to create a new database and new tables for each new customer.

CodePudding user response：

How can I add to my script? @O. Jones

echo -e "$BCyan =========================================================================" echo -e "$BCyan Creating Databases and User for SuiteCRM... " echo -e "$BCyan =========================================================================" echo -e "$Color_Off" sleep 1

mysql -u root -e "SHOW DATABASES"

echo -en "$BWhite Please Enter Databases Name $BYellow (default $DB_NAME_DEF):$BGreen" read DB_NAME DB_NAME="${DB_NAME:-$DB_NAME_DEF}"

echo -en "$BWhite Please Enter Databases Username $BYellow (default $DB_USER_DEF):$BGreen" read DB_USER DB_USER="${DB_USER:-$DB_USER_DEF}"

echo -en "$BWhite Please Enter Databases Password $BYellow (default $DB_PASSWD_DEF):$BGreen" read DB_PASSWD DB_PASSWD="${DB_PASSWD:-$DB_PASSWD_DEF}"

echo -e "$Color_Off"

RESULT=mysql -u root -e "SHOW DATABASES" | grep $DB_NAME if [ "$RESULT" == "$DB_NAME" ]; then echo -e "$BYellow The Database Named $BRed $DB_NAME $Color_Off $BYellow already exists $Color_Off" echo -e "$BWhite We Suggest Trying the New Database Name $Color_Off" exit 1 else echo -e "$BYellow The Database Name $DB_NAME Has Been Created Successfully! $Color_Off" mysql -u root -e "CREATE DATABASE $DB_NAME"; mysql -u root -e "CREATE USER '$DB_USER'@'localhost' IDENTIFIED BY '$DB_PASSWD'"; mysql -u root -e "GRANT ALL PRIVILEGES ON $DB_NAME.* TO '$DB_USER'@'localhost'"; mysql -u root -e "FLUSH PRIVILEGES"; fi