If you are reading this blog post, you likely already know the purpose of user-defined functions (UDF) in MySQL. If you need a quick refresher on UDF’s take a look at the MySQL reference manual.  You may have created your own UDFs as well.…

Facebook Twitter LinkedIn

Tweet

I few months ago, I wrote about using the Django framework with MySQL 8. There are also other Python frameworks that are worth considering. In this blog, I will look at using SQLAlchemy with MySQL 8.

In order for you to be able to use MySQL 8 with SQLAlchemy, you need three pieces of software: MySQL Server, MySQL Connector/Python, and SQLAlchemy. I will go through the installations, then I will look at a code example.

Information

The examples in this blog uses MySQL Server 8.0.15, MySQL Connector/Python 8.0.15, and SQLAlchemy 1.2.18. It should be possible to reuse the instructions with other MySQL versions as well, except in older MySQL versions you …

Tweet

MySQL supports a long list of character sets, and one of the strong points are that you can use different character sets per column. This is very flexible, but which character set should you use?

If you do not want to read the whole blog the summary is: Use utf8mb4, particularly in MySQL 8.0 with one of the UCA 9.0.0 based collations.

Before I reach this conclusion, let’s start out looking at what a character set is.

What is a Character Set?

Characters are wonderful things that can take all kinds of shapes ranging from the familiar (for English speakers at least) Latin characters as those used to write this blog, over Arabic (مرحبا بالعالم) and Asian characters such as simplified Chinese …

When computers were still using large black text oriented screens or no screens at all, a computer only knew how to store a limited set of characters. Then it was normal to store a name with the more complicated characters replaced by more basic characters. The ASCII standard was used to make communication between multiple systems (or applications) easier. Storing characters as ASCII needs little space and is quite strait forward.

Then DOS used CP850 and CP437 and so on to make it possible to use language /location specific characters.
Then ISO8859-1, ISO8859-15 and more of these character sets were defined as standard.

And now there is Unicode: UTF-8, UTF-16, UCS2, etc. which allow you to …

A recent question on a mailing list was the best practices for UTF-8 and PHP/MySQL. The following are the configurations I used in my multi-language projects.

MySQL UTF-8 Configuration

# my.cnf
[mysqld]
default_character_set = utf8
character_set_client       = utf8
character_set_server       = utf8
[client]
default_character_set = utf8

PHP UTF-8 Configuration

#php.ini
default_charset = "utf-8"

Apache UTF-8 Configuration

#httpd.conf
AddDefaultCharset UTF-8
<VirtualHost>
    AddCharset UTF-8   .htm
</VirtualHost>

HTML file UTF-8 Configuration

 <meta charset="utf-8">

PHP file UTF-8 Configuration

header('Content-type: text/html; charset=UTF-8');

MySQL connection (extra precaution)

SET NAMES utf8;

Shell UTF-8

And last but not least, even editing files in shell can be affected (.e.g UTF-8 data to be …

Having reviewed different table structures designed by different people, I have come to the conclusion that binary and non-binary string data types are used without consideration of the consequences of choosing either one. The confusion stems from the fact that both non-binary and binary string data appear to store characters because they can be saved as quoted string.

Background Knowledge

The MySQL database v4.0.23 is using the default character set of “Latin1″. When the database was created I had no knowledge of character sets other wise it would have been “UTF-8″.
The web pages are using a character set of “UTF-8″.

Problem

Data being queried from a MySQL database that contains French accent characters will not render correctly in the browser even after applying PHP htmlentities().

Example code: $string = htmlentities($string , ENT_QUOTES, “UTF-8″);

Solution

The queried data from the database was inputted using the character set “ISO-8859-1″. I …