MySQL – Page 57

MySQL length() vs char_length()

November 19, 2019April 30, 2018 by Ian

In MySQL, there are many times where the length() function and the char_length() function will provide exactly the same results. However, there are also times where the results will be completely different. Here’s why.

First, here’s the definition for each of these functions:

char_length(): Returns the length of a string, measured in characters.
length(): Returns the length of a string, measured in bytes.

Notice “characters” vs “bytes” – one is measured in characters, the other is measured in bytes.

In many cases, the number of bytes will be the same as the number of characters in the string, but this isn’t always the case. The number of bytes used per character depends on how the data is stored. For example, if the string is stored as Unicode data, there will be 2 bytes per character.

What is CHAR_LENGTH() in MySQL?

February 14, 2020April 30, 2018 by Ian

In MySQL, the CHAR_LENGTH() function returns the length of a string, measured in characters. MySQL also has CHARACTER_LENGTH(), which is a synonym for CHAR_LENGTH().

Here’s an example:

SELECT CHAR_LENGTH('Lit');

And here’s the result:

+--------------------+
| CHAR_LENGTH('Lit') |
+--------------------+
|                  3 |
+--------------------+

What is LENGTH() in MySQL?

February 14, 2020April 30, 2018 by Ian

One of the many functions in MySQL is the LENGTH() function, which returns the length of a string, measured in bytes.

Example:

SELECT LENGTH('Lit');

Result:

+---------------+
| LENGTH('Lit') |
+---------------+
|             3 |
+---------------+

This is a simple example and the result is the same as if we’d used the CHAR_LENGTH() function. However, the LENGTH() function can return different results, depending on the data type.

How to Set the Character Set and Collation of a Database in MySQL

February 14, 2020April 25, 2018 by Ian

In MySQL, you can specify the character set and collation at various levels. You can specify them at the connection level, the server level, the database level, the table level, and the column level. You can also specify a collation in your queries so that it overrides any collation that has been previously specified at the aforementioned levels.

To set the character set and collation at the database level, you can use the CREATE DATABASE statement or ALTER DATABASE statement (depending on whether you’re creating the database or modifying it).

How to Set the Character Set and Collation of a Table in MySQL

February 14, 2020April 25, 2018 by Ian

To set the character set and collation at the table level, you can use the CREATE TABLE statement or ALTER TABLE statement (depending on whether you’re creating the table or modifying it).

How to Set the Character Set and Collation of a Column in MySQL

February 14, 2020April 25, 2018 by Ian

To set the character set and collation at the column level, you can use the CREATE TABLE statement or ALTER TABLE statement (depending on whether you’re creating the table or modifying it), and specify the character set and collation within the column’s definition (the column that you want to set the character set/collation on).

How to Show the Collation of your Connection in MySQL

February 14, 2020April 24, 2018 by Ian

When you run a query against a MySQL database, MySQL uses a bunch of system variables to determine which character set and collation to use whenever queries are run. If the client uses a different character set to the server, then MySQL can translate it into an appropriate character set and collation.

When sending the query results back to the client, MySQL can translate these results back to a different character set altogether if required. MySQL uses system variables to determine which character sets and collations to use at each of these steps.

The following singles out the connection collation:

SELECT @@collation_connection;

Example result:

+------------------------+
| @@collation_connection |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+

How to Show the Collation of a Database in MySQL

February 14, 2020April 24, 2018 by Ian

This article provides three ways to return the collation of a database in MySQL.

The following statement can be used to check the default character set and collation for a given database:

USE Music;
SELECT @@character_set_database, @@collation_database;

Example result:

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+

This example shows the collation for a database called Music. First, we switch to that database, then we do the SELECT statement to return system variables for the character set and the collation.

How to Show the Server Collation in MySQL

February 14, 2020April 24, 2018 by Ian

Running the following command returns the server’s default collation.

SELECT @@collation_server;

Example result:

+--------------------+
| @@collation_server |
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+

This returns the collation_server system variable, which contains the collation of the server. However, this isn’t the only way to do it.

How to Show the Collation of a Column in MySQL

February 14, 2020April 24, 2018 by Ian

This page contains three ways of returning the collation of a column in MySQL.

Running the following query is the quickest way to return the collation of a column. In particular, it returns information about each column in a given table. This includes the collation information.

SHOW FULL COLUMNS FROM Artists;

That results in a lot of columns being returned with all sorts of information about the column, including the collation. To reduce the number of columns returned, see below.