Description:
The new tablespace changes are excellent, but to make use of them in existing applications which don't support "TABLESPACE" keyword for CREATE TABLE commands, or for restoring backups from dumps and probably other cases it could be easier to use.
How to repeat:
mysql> CREATE DATABASE `foo`;
mysql> CREATE TABLESPACE foo_ts ADD DATAFILE '/var/lib/mysql_datafiles/foo.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.16 sec)
mysql> use foo;
Database changed
mysql> create table t(i int) ENGINE=InnoDB;
The table is not created in the `foo_ts` tablespace unless explicitly set in the CREATE TABLE command.
Suggested fix:
I suggest there should be a session variable which can be used to specify the default TABLESPACE value when it is not defined explicitly.
Example syntax:
```
mysql> USE TABLESPACE `my_tablespace`;
```
Or:
```
mysql> SET default_tablespace = 'my_tablespace';
```
This way one statement could be executed at the beginning of the connection which would affect all subsequent CREATE TABLE commands so that if `... TABLESPACE ...` is not defined the set one is used instead of the default one.
It could also be a global variable so that it could be set by a super user for rotating tablespaces.
Additionally, it would also be great to bind a user to a tablespace either exclusively, or just by default.
Example:
```
mysql> CREATE USER 'foo'@'%' TABLESPACE `foo_tablespace`;
```
So that the user's tables are always created in `foo_tablespace` by default.
Thanks!
