Schema Definition

Create Database

A good default database create statement is as follows:
CREATE DATABASE my_new_db CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
You may need something different but this is my "starter for 10".

Create Tables

In general this is a simple thing to do but often you need to know the syntax for these things, so see the following for help:

Examine Database

To see how databases were created, so what collation they are using, then execute this:
select * from information_schema.schemata;
This reports the following:

  • CATALOG_NAME - the catalog name is always "def"
  • SCHEMA_NAME - this is the name of the database
  • DEFAULT_CHARACTER_SET_NAME - this is usually something related to UTF
  • DEFAULT_COLLATION_NAME - look closely to see if this is "ci" (case insensitive) or "cs" (case sensitive)
  • SQL_PATH - always NULL
You can then use the following for creating a new database:
CREATE DATABASE my_new_db CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';

If you want to see how to recreate all of your databases then use this:
SELECT CONCAT('CREATE DATABASE ', schema_name, ' CHARACTER SET = ''', default_character_set_name, ''' COLLATE = ''', default_collation_name, ''';') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema');
The "system" databases are excluded as you will never be creating those yourself. They have different uses as follows:

  • mysql - this is the main system database containing permissions and other stuff for MySQL to work
  • sys - helps work with performance_schema
  • performance_schema - used to monitor low level server execution
  • information_schema - this contains all the metadata on the other schemas

Examining Schema

There are a few useful commands I seem to use a lot from within the mysql command line tool:

  • show databases; - show a list of databases the current user has access to
  • use my_db; - change the default database to my_db
  • show tables; - output a list of tables in the current database
  • show full tables; - as above but identify table or view
  • desc my_table; - display the definition of my_table
  • show create table my_table - display the syntax of the "create table" command to create my_table
  • show index from my_table - output index information on my_table
  • show procedure status; - list all procedures over all databases
  • show function status; - list all the functions
  • show procedure status where db = database(); - show procedures for current database
  • show create procedure my_procedure; - show the definition of the procedure
  • show create procedure my_procedure\G - show the definition of the procedure but in an easier to read format!
  • show create function my_function; - show the definition of the procedure
  • select * from information_schema.routines; - handy query

Getting all of the constraints is a little more tricky but the following will do it:
select * from information_schema.table_constraints where constraint_schema = 'my_db';
select * from information_schema.key_column_usage where constraint_schema = 'my_db';
select * from information_schema.referential_constraints where constraint_schema = 'my_db';

Data Types

Both MySQL and MariaDB support the common data types and they have a lot of overlap but also some minor differences. So check which supports what see the following documentation:

Updating Tables

MySQL allows you to specify where the new column appears in the table structure, so for example:
ALTER TABLE test_table ADD COLUMN price DECIMAL(16,2) AFTER status;
However, you can replace "AFTER status" with "FIRST" if you want the new column to be the first one.

You can rename a table as follows:
RENAME TABLE my_table TO new_table;
You can use this to move tables between databases by specifying different database names before the tables names, as long as the databases use the same filesystem:
RENAME TABLE db1.my_table TO db2.new_table;

Changing Columns

After creating your table you can also very easily change it, so let's say you want to alter a column name. First off have a look at the current schema with the describe command, which can be abbreviated this:
DESC my_table;
Make a note of the current column type and then you can do this
ALTER TABLE my_table CHANGE current_name new_name INTEGER;
This of course assumes it is an integer column but you do have to specify the column type. This of course means that you can change the colun type:
ALTER TABLE my_table CHANGE current_name current_name VARCHAR(20);
However this is more clearly done as:
ALTER TABLE my_table MODIFY current_name VARCHAR(20);
Note that with both CHANGE and MODIFY you can specify "FIRST" or "AFTER Col1" to move the column in the order.

Indexes

There are three main index types as follows:

  • PRIMARY KEY - this is a UNIQUE index which does not allow nulls, note that using too many columns severely impacts storage
  • UNIQUE - index of unique values but allows multiple rows with NULL
  • KEY - a regular index
So, an observation of the above is that if you have a single field that is a unique identifier, then it should be the PRIMARY KEY but do not also give it a UNIQUE index, that is duplication, even though graphical tools might permit this. It is always worth eyeballing the generated DDL to see if it makes sense.

Changing Indexes

If you wish to add a column to the primary key, then I think you need to drop it first and then add it as follows:
ALTER TABLE my_table DROP PRIMARY KEY;
ALTER TABLE my_table ADD PRIMARY KEY (Col1, Col2);
Hopefully I can prove myself wrong at some point.

Exporting Schema

You can use the mysqldump command with "--no-data" to export the database schema. I believe the issue here is you need permissions on the Information Schema or something like that. If you don't have this but you can query the database then you can connect with the mysql command, then use the following:
show create table databasename.tablename; this will show the SQL that would be needed to create the table.
Alternatively you can use the following and even redirect the output to a file
mysql --execute="show create table ddddd.ttttt"
You may need to specify host, username and password, which is expained in the "Tools and Utilities" section of MySQL.