MariaDB Alter Database

Summary: in this tutorial, you will learn how to use the MariaDB alter database statement to change the characteristics of a database.

Introduction to MariaDB alter database statement

The alter database statement changes the characteristics of a database. Here is the syntax of the alter database statement:

alter database [database_name]
Code language: SQL (Structured Query Language) (sql)

[character set charset_name]

[collate collation_name]

In this syntax:

• First, specify the name of the database that you want to change. If you skip it, the alter database statement is applied to the current database.
• Second, specify the new character set and collation for the database in the character set and collate clauses.

To select a correct character set and collation, you use the show character set and show collation statements respectively.

Note that you need to have the alter privilege on the database that you want to change in order to execute the alter database statement successfully.

MariaDB alter database statement example

First, show all databases available in the current server:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| crm                |
| information_schema |
| mysql              |
| nation             |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.01 sec)
Code language: SQL (Structured Query Language) (sql)

Second, use the show create database to examine the characteristics of the crm database:

mysql> show create database crm;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| crm      | CREATE DATABASE `crm` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

Third, show the available character set and default collation in the current server by using the show character set statement:

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
...
Code language: SQL (Structured Query Language) (sql)

Fourth, change the character set of the crm database to latin1 and collation to latin1_swedish_ci by using the alter database statement to

mysql> alter database crm
    -> character set = 'latin1'
    -> collate = 'latin1_swedish_ci';
Query OK, 1 row affected (0.01 sec)
Code language: SQL (Structured Query Language) (sql)

Finally, verify the changes by using the show create database statement:

mysql> show create database crm;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| crm      | CREATE DATABASE `crm` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MariaDB alter database statement to change the character set and collation of a database.