Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’

Searching for answers to an identical “Illegal mix of collations” error with conflicts between utf8_unicode_ci and utf8_general_ci. I found that some columns in the database were not specifically collated utf8_unicode_ci. It seems mysql implicitly collated these columns utf8_general_ci. Specifically, running a ‘SHOW CREATE TABLE table1’ query outputted something like the following: | table1 | CREATE TABLE `table1` ( `id` int(11) NOT NULL, `col1` varchar(4) CHARACTER SET utf8 NOT NULL, `col2` int(11) NOT NULL, PRIMARY KEY (`photo_id`,`tag`) ) ENGINE=InnoDB DEFAULT…

Read More

Posted in MySQL Tagged Comments Off on Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’
MySQL DELETE JOIN

This quick reference introduces you a more flexible way to delete data from multiple tables by using INNER JOIN or LEFT JOIN clause in the DELETE statement. MySQL DELETE JOIN with INNER JOIN Syntax MySQL DELETE JOIN with INNER JOIN Example MySQL DELETE JOIN with LEFT JOIN Example

Read More

Posted in MySQL Tagged Comments Off on MySQL DELETE JOIN
MySQL TRIM() function

MySQL TRIM() function returns a string after removing all prefixes or suffixes from the given string. Syntax TRIM( str) Arguments Name Description BOTH Indicates that prefixes from both left and right are to be removed. LEADING Indicates that only leading prefixes are to be removed. TRAILING Indicates that only trailing prefixes are to be removed. remstr The string to be removed. FROM Keyword str The actual string from where remstr is to…

Read More

Trigger That Will Remove All Blanks Spaces From a Field

If you are importing data into a table and some of the data like a customer fields has blank spaces on it and you want to get rid of then a trigger for Insert event may a quick solution. Here is a request: “ Is it possible to create a trigger that will remove all blanks from a field? They have an check number and when we read it through Import process, it keeps putting random spaces in the…

Read More

Posted in MySQL, SQL Tagged , Comments Off on Trigger That Will Remove All Blanks Spaces From a Field
SQL SELECT TOP Clause

The SQL SELECT TOP Clause The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance. Note: Not all database systems support the SELECT TOP clause. SQL Server / MS Access Syntax SELECT TOP number|percent column_name(s) FROM table_name;   SQL SELECT TOP Equivalent in MySQL and Oracle MySQL Syntax SELECT column_name(s) FROM table_name LIMIT number; Example SELECT *…

Read More