MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Function Overview

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL Default Character Set And Collation Rules

MySQL uses character sets and collations to manage the storage and comparison of text data. A character set is a collection of characters and their corresponding encodings, while a collation defines the rules for comparing characters within a character set.

Default Character Set and Collation:

MySQL has default settings for the character set and collation at different levels: server, database, table, and column. In the absence of explicit character set or collation declarations, these default settings are applied.

The MySQL server has a default character set and collation, which are applied when creating a new database or table. These default settings can be viewed by running the following SQL commands:

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

In MySQL 8.0 and later, the default character set is utf8mb4 and the default collation is utf8mb4_0900_ai_ci. In MySQL 5.7 and earlier, the default character set is utf8 and the default collation is utf8_general_ci.

Changing the Default Character Set and Collation:

Server level: To change the default character set and collation at the server level, edit your MySQL server configuration file (usually my.cnf on Unix-like systems or my.ini on Windows) and add or modify the following lines in the [mysqld] section:
```
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
```
Replace utf8mb4 and utf8mb4_unicode_ci with your desired character set and collation. After making changes, restart your MySQL server for the new settings to take effect.
Database level: To change the default character set and collation for a specific database, use the ALTER DATABASE statement:
```
ALTER DATABASE database_name
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```
Replace database_name, utf8mb4, and utf8mb4_unicode_ci with your database name and desired character set and collation.
Table level: To change the default character set and collation for a specific table, use the ALTER TABLE statement:
```
ALTER TABLE table_name
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```
Replace table_name, utf8mb4, and utf8mb4_unicode_ci with your table name and desired character set and collation.
Column level: To change the character set and collation for a specific column, use the ALTER TABLE statement with the MODIFY COLUMN clause:
```
ALTER TABLE table_name
MODIFY COLUMN column_name VARCHAR(255)
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```
Replace table_name, column_name, VARCHAR(255), utf8mb4, and utf8mb4_unicode_ci with your table name, column name, data type, and desired character set and collation.

By understanding the default character set and collation rules in MySQL, you can ensure that your text data is stored and processed correctly, and that the appropriate character set and collation are used for your specific requirements.

How to check default character set in MySQL:
- Use the following SQL query to check the default character set:
```
SHOW VARIABLES LIKE 'character_set_server';
```
Setting default character set in MySQL server:
- Configure the default character set in the MySQL server configuration file (my.cnf or my.ini):
```
[mysqld]
character_set_server=utf8mb4
```
Changing default character set in MySQL:
- Modify the default character set in the MySQL server configuration file:
```
[mysqld]
character_set_server=utf8mb4
```
MySQL server-wide default character set:
- Set the default character set for the entire MySQL server:
```
SET GLOBAL character_set_server = utf8mb4;
```
Default character set in MySQL configuration:
- Specify the default character set in the MySQL configuration file or during server startup:
```
[mysqld]
character_set_server=utf8mb4
```
MySQL global default collation settings:
- Global collation settings for the server can be configured in the MySQL configuration file:
```
[mysqld]
collation-server=utf8mb4_general_ci
```
MySQL database default character set:
- Set the default character set for a specific database:
```
CREATE DATABASE your_database CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
```
UTF-8 as the default character set in MySQL:
- UTF-8 is a popular choice as the default character set for its comprehensive support for Unicode characters.
```
[mysqld]
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci
```