UTF-8 Archives | phpGrid - PHP Datagrid Create PHP grids in minutes, not hours. Wed, 28 Jul 2021 01:25:57 +0000 en-US hourly 1 CodeProjecthttps://wordpress.org/?v=7.0.3 129966043 MySQL Character Sets & Collation https://phpgrid.com/blog/mysql-character-sets-collation/ Wed, 28 Jul 2021 01:24:23 +0000 https://phpgrid.com/?p=9720

In this post, we will focus on a specific type of character set and encoding, MySQL CHARSET & COLLATION. I also recommend checking out the earlier post on character encoding for beginners. Have you ever wondered why some non-English characters with accents or Chinese characters such as “豆贝尔维 ” showing as question marks ���� on […]

The post MySQL Character Sets & Collation appeared first on phpGrid - PHP Datagrid.

]]>

In this post, we will focus on a specific type of character set and encoding, MySQL CHARSET & COLLATION. I also recommend checking out the earlier post on character encoding for beginners.

Have you ever wondered why some non-English characters with accents or Chinese characters such as “豆贝尔维 ” showing as question marks ���� on a web page?

TL:DR;

  1. Best practice is to go completely utf8mb4.
    1
    SET NAMES utf8mb4
  2. Set column collation to utf8mb4_0900_ai_ci
    1
    ALTER TABLE MODIFY VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
  3. On your web page include
    1
    <meta http-equiv="Content-type" content="text/html; charset=UTF-8"></span>

MySQL Character Sets & Collation

MySQL supports various character sets to store almost every character in a string. You can find out the current character set supported by using the following MySQL command:

1
mysql> SHOW CHARACTER SET;

‘Collation’ refers to how those characters could be compared for inequality in WHERE and ORDER BY, GROUP BY, FOREIGN KEY CONSTRAINTS, and strings case (in)sensitive comparison.

Even though MySQL has been around since 1996, it didn’t start supporting utf8 encoding until 2004 in version 4. Before that, only English characters can be stored. In this version, instead of utf8, the default charset / collation was set to “latin1 / latin1_swedish_ci“.

MySQL’s UTF8 problem

Although the name suggests UTF-8, MySQL’s utf8 encoding is a proprietary character encoding that only supported three bytes per character. The actual UTF-8 encoding needs up to four bytes per character, as discussed in the “Character set for beginners” post. As a result, the encoding can’t encode many Unicode characters.

In 2010 (six years later!), MySQL finally fixed this embarrassing oversight of the utf8 encoding bug in version 5.5 with a new character set called “utf8mb4″. It’s never publicly announced, probably too ashamed to admit such humiliating mistakes. Consequently, few people knew about the significance of the change and remained in the dark with the old (and wrong) encoding.

If you are running MySQL before 5.5, you got only ‘utf8‘. You are out of luck if you need to store and display Emoji 👌 or Chinese 汉字. You will need ‘utf8mb4‘, which is only available in MySQL 5.7+.

Fortunately, since MySQL 8 (released in 2018), utf8mb4 becomes the default character set with collation utf8mb4_0900_ai_ci. We can finally put that MySQL Unicode encoding madness behind us.

What about MariaDB?

MariaDB suffered from the same fate as MySQL in UTF8 encoding. Fortunately, it has added utf8mb4 support since version 5.5 in 2012.

Other Character Set Problems

Besides MySQL historical utf8 bug, there are additional three factors to character set problems (apply to all databases):

  1. Client’s bytes encoding;
  2. What SET NAMES you use (or what the default is);
  3. The CHARACTER SET on the column definition.

 
All is well if the SET NAMES agrees with the encoding of the bytes in the Clients.

CHARACTER SET in column definition needs not to agree with SET NAMES; if they differ, a conversion will be performed for you. If characters exist in both encodings, the conversion will be transparent.

The problems occur when SET NAMES disagree with the Client’s bytes.

If a source character does not exist in the target encoding (example: when converting a Chinese character from utf8mb4 to latin1), a “�” is usually put in its place since Chinese has way more characters than what is in Latin.

MySQL/MariaDB Character Encoding Best Practice

To wrap up, you should upgrade to MySQL 8, and go completely utf8mb4.

1
SET NAMES utf8mb4

In addition, for web content, a web page (if that is what you have) should always include following in <head> section:

1
<meta http-equiv="Content-type" content="text/html; charset=UTF-8">

Recommended readings:

The post MySQL Character Sets & Collation appeared first on phpGrid - PHP Datagrid.

]]> 9720 Character Encodings for Beginners https://phpgrid.com/blog/character-encodings-for-beginners/ Tue, 27 Jul 2021 21:31:32 +0000 https://phpgrid.com/?p=9722

Character encoding governs how characters travel from databases to web browsers and back again. It has a long and winding history behind characters and their encoding. Byte & Character A byte has 8 bits that can represent 256 different values. That’s more than enough for all the English characters plus additional numerical digits, common punctuation […]

The post Character Encodings for Beginners appeared first on phpGrid - PHP Datagrid.

]]>

Character encoding governs how characters travel from databases to web browsers and back again. It has a long and winding history behind characters and their encoding.

Byte & Character

A byte has 8 bits that can represent 256 different values. That’s more than enough for all the English characters plus additional numerical digits, common punctuation marks, spaces, tabs, and other control characters. ASCII character encoding has become an 8-bit character encoding standard for electronic communication since the 1960s.

However, since many non-English languages have more than 256 characters, such as Korean (over 11,000 characters) and Chinese (40,000+ ), it is impossible to squeeze many characters into a single byte. To hold the non-English characters, a character must be composed of one or more bytes.

Unicode – A New Character Set Standards

In the late 1980s, a new standard was developed – Unicode – a modern encoding system that can produce over a million code points, more than enough to account for every character in any language. Unicode is the new universal standard for encoding all human languages. It even includes emojis.

Unicode is a character standard, not character encoding. Computers need a way to translate Unicode into binary in order to store them in text files. Here’s where UTF-8 comes in.

UTF-8 Encoding

UTF-8 is an encoding system for Unicode. UTF-8 uses a creative multi-byte variable-width encoding method to save storage while accommodating multi-byte characters. In UTF-8, some characters like X take only 1 byte, and some characters like emoji 😄 can take as much as 4.

Best of all, UTF-8 is backward compatible with ASCII.

There are three different varieties of Unicode character encodings: UTF-8, UTF-16, and UTF-32. Of these three. phpGrid already uses UTF-8 character encoding for all of the demos on Demo Explorer. In fact, Only UTF-8 should be used for web content.

Since an HTML page can only be in one encoding, a Unicode-based encoding such as UTF-8 can support many languages and accommodate pages and forms in any mixture of those languages (source: w3.org).

What Should You Do As a Web Developer?

At the time of this writing, UTF-8 is used by 96.4% of all the websites whose character encoding we know.

Web page should include following in <head> section

1
<meta http-equiv="Content-type" content="text/html; charset=UTF-8">

<meta charset= “utf-8”> tells the browser to use the utf-8 character encoding when translating machine code into human-readable text and vice versa to be displayed in the browser.

The good news is that the default character encoding used in HTML5 is already UTF-8. Include the following

1
<!DOCTYPE html>

at the top of your HTML file (which declares that it’s an HTML5 file) should automatically set your web page as UTF-8 unless specified otherwise in the aforementioned meta element.

Just remember only UTF-8 should be used for web content… And happy encoding!

Recommended reading:
https://www.w3.org/International/questions/qa-choosing-encodings

The post Character Encodings for Beginners appeared first on phpGrid - PHP Datagrid.

]]> 9722