Open-source News

AMD 4th Gen EPYC "Genoa" To Enjoy Slightly Better Performance ...  Phoronix

A guide to strings in MySQL HunterC Tue, 01/10/2023 - 03:00

Strings are one of the most common data types you will use in MySQL. Many users insert and read strings in their databases without thinking too much about them. This article aims to give you a bit of a deep dive into how MySQL stores and displays your string variables so that you can have better control over your data.

You can break strings into two categories: binary and nonbinary. You probably think about nonbinary strings most of the time. Nonbinary strings have character sets and collations. Binary strings, on the other hand, store things such as MP3 files or images. Even if you store a word in a binary string, such as song, it is not stored in the same way as in a nonbinary string.

I will focus on nonbinary strings. All nonbinary strings in MySQL are associated with a character set and a collation. A string's character set controls what characters can be stored in the string, and its collation controls how the strings are ordered when you display them.

Character sets

To view the character sets on your system, run the following command:

SHOW CHARACTER SET;

This command will output four columns of data, including the character set:

• Name
• Brief description
• Default collation
• Maximum size of each character in the character set

MySQL used to default to the latin1 character set, but since version 8.0, the default has been utf8mb4. The default collation is now utf8mb4_0900_ai_ci. The ai indicates that this collation is accent insensitive (á = a), and the ci specifies that it is case insensitive (a = A).

Different character sets store their characters in various-sized chunks of memory. For example, as you can see from the above command, characters stored in utf8mb4 are stored in memory from one to four bytes in size. If you want to see if a string has multibyte characters, you can use the CHAR_LENGTH() and LENGTH() functions. CHAR_LENGTH() displays how many characters a string contains, whereas LENGTH() shows how many bytes a string has, which may or may not be the same as a string's length in characters, depending on the character set. Here is an example:

SET @a = CONVERT('data' USING latin1);

SELECT LENGTH(@a), CHAR_LENGTH(@a);

+------------+-----------------+
| LENGTH(@a) | CHAR_LENGTH(@a) |
+------------+-----------------+
|     4      |       4         |
+------------+-----------------+

This example shows that the latin1 character set stores characters in single-byte units. Other character sets, such as utf16, allow multibyte characters:

SET @b = CONVERT('data' USING utf16);

SELECT LENGTH(@b), CHAR_LENGTH(@b);

+------------+------------------+
| LENGTH(@b) | CHAR_LENGTH(@b)  |
+------------+------------------+
|       8    |        4         |
+------------+------------------+Collation

A string's collation will determine how the values are displayed when you run a SQL statement with an ORDER BY clause. Your choice of collations is determined by what character set you select. When you ran the command SHOW CHARACTER SET above, you saw the default collations for each character set. You can easily see all the collations available for a particular character set. For example, if you want to see which collations are allowed by the utf8mb4 character set, run:

SHOW COLLATION LIKE 'utf8mb4%';

A collation can be case-insensitive, case-sensitive, or binary. Let's build a simple table, insert a few values into it, and then view the data using different collations to see how the output differs:

CREATE TABLE sample (s CHAR(5));

INSERT INTO sample (s) VALUES
 ('AAAAA'), ('ccccc'),  ('bbbbb'), ('BBBBB'), ('aaaaa'), ('CCCCC');

SELECT * FROM sample;

+-----------+
| s         |
+-----------+
| AAAAA     |
| ccccc     |
| bbbbb     |
| BBBBB     |
| aaaaa     |
| CCCCC     |
+-----------+

With case-insensitive collations, your data is returned in alphabetical order, but there is no guarantee that capitalized words will come before lowercase words, as seen below:

SELECT * FROM sample ORDER BY s COLLATE utf8mb4_turkish_ci;

+-----------+
| s         |
+-----------+
| AAAAA     |
| aaaaa     |
| bbbbb     |
| BBBBB     |
| ccccc     |
| CCCCC     |
+-----------+

On the other hand, when MySQL runs a case-sensitive search, lowercase will come before uppercase for each letter:

SELECT * FROM sample ORDER BY s COLLATE utf8mb4_0900_as_cs;

+-----------+
| s         |
+-----------+
| aaaaa     |
| AAAAA     |
| bbbbb     |
| BBBBB     |
| ccccc     |
| CCCCC     |
+-----------+

And binary collations will return all capitalized words before lowercase words:

SELECT * FROM sample ORDER BY s COLLATE utf8mb4_0900_bin;

+-----------+
| s         |
+-----------+
| AAAAA     |
| ccccc     |
| bbbbb     |
| BBBBB     |
| aaaaa     |
| CCCCC     |
+-----------+

If you want to know which character set and collation a string uses, you can use the aptly named charset and collation functions. A server running MySQL version 8.0 or higher will default to using the utf8mb4 character set and utf8mb4_0900_ai-ci collation:

SELECT charset('data');

+-------------------+
| charset('data')   |
+-------------------+
| utf8mb4           |
+-------------------+

 SELECT collation('data');

+--------------------+
| collation('data')  |
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+

You can use the SET NAMES command to change the character set or collation used.

To change from the utf8mb4 character set to utf16, run this command:

SET NAMES 'utf16';

If you would also like to choose a collation other than the default, you can add a COLLATE clause to the SET NAMES command.

For example, say your database stores words in the Spanish language. The default collation for MySQL (utf8mb4_0900_ai_ci) sees ch and ll as two different characters and will sort them as such. But in Spanish, ch and ll are individual letters, so if you want them sorted in the proper order (following c and l, respectively), you need to use a different collation. One option is to use the utf8mb4_spanish2_ci collation.

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_spanish2-ci';

More on data science What is data science? What is Python? How to become a data scientist Data scientist: A day in the life Use JupyterLab in the Red Hat OpenShift Data Science sandbox Whitepaper: Data-intensive intelligent applications in a hybrid cloud blueprint MariaDB and MySQL cheat sheet Latest data science articles Storing strings

MySQL allows you to choose between several data types for your string values. (Even more so than other popular databases such as PostgreSQL and MongoDB.)

Here is a list of MySQL's binary string data types, their nonbinary equivalents, and their maximum length:

• binary: char (255)
• varbinary: varchar (65,535)
• tinyblob: tinytext (255)
• blob: text (65,535)
• mediumblob: mediumtext (16,777,215)
• longblob: longtext (4,294,967,295)

One important thing to remember is that unlike the varbinary, varchar, text, and blob types, which are stored in variable length fields (that is, using only as much space as needed), MySQL stores binary and char types in fixed length fields. So a value such as char(20) or binary(20) will always take up 20 bytes, even if you store less than 20 characters in them. MySQL pads the values with the ASCII NUL value (0x00) for binary types and spaces for char types.

Another thing to consider when choosing data types is whether you want spaces after the string to be preserved or stripped. When displaying data, MySQL strips whitespace from data stored with the char data type, but not varchar.

CREATE TABLE sample2 (s1 CHAR(10), s2 VARCHAR(10));

INSERT INTO sample2 (s1, s2) VALUES ('cat       ', 'cat       ');

SELECT s1, s2, CHAR_LENGTH(s1), CHAR_LENGTH(s2) FROM sample2;

+---------+---------+-----------------------------------+
| s1      | s2      | CHAR_LENGTH(s1) | CHAR_LENGTH(s2) |
+---------+---------+-----------------------------------+
| cat     | cat     |        3        |       10        |
+---------+---------+-----------------------------------+Wrap up

Strings are one of the most common data types used in databases, and MySQL remains one of the most popular database systems in use today. I hope that you have learned something new from this article and will be able to use your new knowledge to improve your database skills.

Learn how MySQL stores and displays your string variables so that you can have better control over your data.

Image by:

Opensource.com

Databases What to read next This work is licensed under a Creative Commons Attribution-Share Alike 4.0 International License. Register or Login to post a comment.

The post 6 Deprecated Linux Commands and Alternative Tools You Should Use first appeared on Tecmint: Linux Howtos, Tutorials & Guides .

Linux provides tons of command-line utilities to perform various tasks. However, with the passage of time, some of these tools have become outdated and replaced by other alternative command-line tools. In this guide, we

The post 6 Deprecated Linux Commands and Alternative Tools You Should Use first appeared on Tecmint: Linux Howtos, Tutorials & Guides.

Making It Easier To Break The SONiC Barrier For Networking  The Next Platform

How to Learn Ethical Hacking Online?  Business Review

You Can Now Install Linux Kernel 6.1 on Ubuntu, Here’s How  9to5Linux

What Is Oracle VirtualBox and What Can You Do With It?  MUO - MakeUseOf

Microsoft: Kubernetes clusters hacked in malware campaign via PostgreSQL  BleepingComputer

For those making use of AMD's Optimizing C/C++/Fortran compilers, ZenNN library, profiling software, and various other CPU-based software resources for EPYC and Ryzen processors, AMD is in the process of rolling out a new area on the website for highlighting these Zen Software Studio assets...

Building a Retro Linux Gaming Computer - Part 20: The Antediluvian ...  GamingOnLinux

How to Use KDE Discover to Manage Linux Apps, Widgets, and ...  MUO - MakeUseOf

Multiplayer voxel RPG 'Veloren' v0.14 out with musical instruments ...  GamingOnLinux

Linux Software Market Next Big Thing | Major Giants Google, Canonical, Plesk, Elementary  openPR

Pixel-art RTS game TFC: The Fertile Crescent adds neutral ...  GamingOnLinux

AMD Ryzen 5 7600 / Ryzen 7 7700 / Ryzen 9 7900 Linux Performance Review  Phoronix

Last week at the AMD CES 2023 keynote hosted by Lisa Su, new 65 Watt Ryzen 7000 series processors were announced. These more affordable Zen 4 processors are going retail this week and today marks the embargo lift. Up on the Linux testing block are the Ryzen 5 7600, Ryzen 7 7700, and Ryzen 9 7900 processors.

OBS Studio recording software v29.0 is out now  GamingOnLinux

Developer of No Man's Sky put up their old Joe Danger playable in ...  GamingOnLinux

Steam breaks more records with 33 million online, and over 10 ...  GamingOnLinux

Back in July Intel engineers published the initial open-source driver code around the new Versatile Processing Unit "VPU" coming with Meteor Lake. This VPU block with 14th Gen Core CPUs is intended for AI inference acceleration for deep learning software...