BTA Blog Rotating Header Image

MySQL

MySQL and UTF-8

I recently had a problem in a web application that I created where the UTF-8 characters were not interpreted correctly by browsers.  The biggest issue was this did not happen in all instances of presenting these UTF-8 strings.

After tracing the strings through several libraries, I found the culprit.  The following SQL statement was the source of the text.

SELECT ID, CONCAT(sDescription, ' (', ID, ')') FROM ProdFamily;

Where ID is a integer key for the table and sDescription is a varchar column.  The result of the CONCAT function is a “binary string” because of the integer column as one of the operands.  The end result is that this “binary string” was treated differently than a regular UTF-8 string and characters outside the normal ASCII ones were not display correctly.  To fix this issue the CAST function must be added to set the type of the ID column to string as follows.

SELECT ID, CONCAT(sDescription, ' (', CAST(ID AS CHAR), ')') FROM ProdFamily

See MySQL CONCAT or CAST function documentation for more information.

MySQL: Setting date field default to current date

In MySQL, creating a date/time stamp field that defaults to the current date/time has been a problem in the past.  MySQL restricted you to one timestamp field per table that is automatically updated with the current date/time.  Early versions of MySQL allowed multiple updating timestamp fields contrary to the specification, but this was “fixed” in later versions.  Then triggers were added to MySQL v5.0.2 and can be used as a solution for this common issue.

CREATE TRIGGER `TableName_dtColumn_default` BEFORE INSERT ON `TableName`  FOR EACH ROW SET NEW.dtColumn = CURRENT_TIMESTAMP;

Replace “TableName” with your table’s name and “dtColumn” with your date/time column’s name.  Also the trigger name, “TableName_dtColumn_default” in this example, must be unique within a schema.

MySQL triggers are a very versatile feature.  For more details about creating and using triggers see the MySQL documentation for CREATE TRIGGER syntax.