Sometimes we may got some sql error like belows:

These because your MySQL server runing strict mode, if you don't want to change your sql statement, a simple solution is switch off the strict mode

find my.ini or my.cnf, add the following line to the end of the file and restart the service (for cPanel , the path may be /etc/ my.cnf)

sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Usually, we will using integer fields to join two table, however, today I have a case need to use a varchar field to join the tables and then get the error "Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation", search the error on google and finally I got the solution as below

SELECT a.* FROM a INNER JOIN b ON CAST(a.field AS CHAR) = CAST(b.field AS CHAR)

There are four case make the overlap exists
(ns, ne, es, ee) = (new_start, new_end, existing_start, existing_end):

1. ns - ne - es - ee: doesn't overlap and doesn't match (because ne < es)
2. ns - es - ne - ee: ovarlaps and matches
3. es - ns - ee - ne: ovarlaps and matches
4. es - ee - ns - ne: doesn't overlap and doesn't match (because ns > ee)
5. es - ns - ne - ee: ovarlaps and matches
6. ns - es - ee - ne: ovarlaps and matches

SQL Statment Below method is found from internet while the second method is my own way usally use.

SELECT * FROM tbl WHERE
existing_start BETWEEN '$newStart' AND '$newEnd' OR
existing_end BETWEEN '$newStart' AND '$newEnd' OR
'$newStart' BETWEEN existing_star AND existing_end

SELECT * FROM tbl WHERE
( existing_start<='$newStart' AND existing_end>'$newStart' )
AND ( existing_start<'$newEnd' AND existing_start>'$newStart' )

Today learn a new method on MySQL. On programing, we always need to judge a record is exists or not, update when exists else insert. I'm always using PHP to do that before, but today I find the that MySQL already have a method to do so. For example, if a table contain three colum a,b,c and a is the unique key or primary key, you can use the following statment to do an update exists else insert.

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

This will insert a new record when the table hasn't a record a=1 else the record will update c=c+1

Table1

Table2

I want to get a result as following:

In this case, if we use LEFT JOIN the result will fetch to 5 columns data, if we use GROUP BY, it can come back with 2 columns of data but the row "value" can only come up with either 1 value of the grouped row due the order priority. So, how can we make the expected result?