MariaDB Alter Database

MariaDB is an open-source relational database management system. In comparison to MySQL, MariaDB possesses several similar features, including ACID compliance, support for a wide range of storage engines, and availability of different data types. As our data needs develop, the demand to modify our databases will become necessary. In MariaDB, the ALTER DATABASE statement comes out as an effective command for altering existing databases. This article will delve into the different facets of modifying databases in MariaDB, including syntax, frequent contexts, and optimal strategies to ensure smooth database transmutation.

ALTER DATABASE Statement in MariaDB

The ALTER DATABASE command in MariaDB gives users the ability to make changes to an existing database. This helpful command serves various changes, including changing database parameters and capabilities. Moreover, let’s go deeper into the syntax and look at some typical cases.

Syntax:

ALTER DATABASE database_name [CHARACTER SET [=] charset_name] [COLLATE [=] collation_name]

Explanation:

• ALTER DATABASE: This is the main statement that indicates that we want to modify an existing database.
• database_name: Put the name of the database we want to change here. This is the database whose attributes we are changing.
• [CHARACTER SET [=] charset_name]: This part is optional and can be used in the case when we want to change the default character set for the given database. As seen in the square brackets it is optional and we can decide to include this part or eliminate it. If included, we would replace charset_name with the desired character set. The = sign is also optional, meaning we can use CHARACTER SET charset_name without the =.
• [COLLATE [=] collation_name]: Similar to the character set, this part is also optional. It allows we to change the default collation for the specified database. If included, replace collation_name with the desired collation. The = sign is, again, optional.

Character Set and Collation are concepts in database management that define how data is stored and compared in terms of characters, particularly text data.

Character Set

• A character set is a set of characters and their distinct representations. It refers to the collection of symbols, characters, and numbers that can be saved and operated in the database.
• In a character set, each character is associated with a unique code called a code point. We can see such character sets as ASCII, UTF-8, UTF-16, and ISO-8859-1.
• Suppose, we have an alphabet with four letters: A, B, a, b. We give each letter a number: A = 0, B = 1, a = 2, b = 3. The letter A is a symbol, the number 0 is the encoding for A, and the combination of all four letters and their encodings is a character set.

Collation

• Collation refers to the rules governing the comparison of characters in a character set. It specifies how characters are arranged and compared in queries, particularly when dealing with string data.
• When comparing or sorting text data, collation is necessary. It ensures that the database knows how to order the characters and strings in the most appropriate culturally and linguistically way.
• MariaDB enable checking database’s collation and character set in several ways. Here’s one:

In this article, We have a Minal database which we will use throughout the article.

Query:

USE Minal; 
SELECT @@character_set_database, @@collation_database;

Output:

Character Set and Collation

Explanation:The output of the above query gives us the character set and collation of ‘Minal‘ database. In the first column it display ‘Minal‘ database character set. In the second column will indicate the collation of the ‘Minal‘ database.

With the ALTER DATABASE statement, it is possible to modify the character set and the database collation type. We may specify the charset_name and collation_name to have the database customized to our changing needs.

Examples of MariaDB Alter Database

Example 1: Changing Character Set and Collation:

This is an example of how to change the character set and collation of a database which is already installed. We will have to choose the appropriate character set and collation based on our data needs.

Query:

ALTER DATABASE Minal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Now we can check whether the character set and collation is changed or not:

Query:

SELECT @@character_set_database, @@collation_database;

Output:

Changed Character Set and Collation

Explanation: As we can see in image that the above character set and collation was different of database named as ‘Minal‘ and now it is changed.

Example 2: Renaming a Database

Renaming a database can be necessary for rebranding or to better reflect the evolving nature of our project. But remember that for renaming a old database as we have the new database created in our application.

Syntax:

ALTER DATABASE OldDatabaseName RENAME TO NewDatabaseName;

Some Important Points

• Backup Your Database: Before running any ALTER DATABASE command, we should back up data. This precaution is taken to ensure we can go back to the initial state when something unexpected happens.
• Consider Impact on Existing Objects: Be aware of the possible effect on current tables, views and stored procedures. Such objects may be slightly modified, and proper planning is vital in this regard.
• Understand Charset and Collation Implications: Data storage and retrieval are impacted by changes to the character set and collation. Comprehend the impact on current data and migration accordingly.

Conclusion

Altering databases is an important capability to know in the constantly changing environment of database management. MariaDB’s ALTER DATABASE statement offers the flexibility that databases need to be modified to new demands. Regardless of whether changing character sets, collations, or other properties, having a careful strategy, following best practices, and being fully aware of the possible consequences will facilitate the faster development of our transactions of MariaDB.