loading
Please wait while loading...

Read more sql-mode-caused-error

Sometimes we may got some sql error like belows:

- Field doesn't have a default values
- Incorrect integer value: '' for column

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

Read more JOIN table by varchar fields - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_genera

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)

Read more Check time/date overlap in MySQL

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' )

Read more If exists update else insert with only MySQL

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

Read more A useful function in MySQL - GROUP_CONCAT

Table1

t_id code name
1 PM123 Hello
2 PM456 Hello2

 

Table2

id t_id value
1 1 something1
2 1 something2
3 1 something3
4 2 baby1
5 2 baby2

 

I want to get a result as following:

t_id code name value value value
1 PM123 Hello something1 something2 something3
2 PM456 Hello2 baby1 baby2  

 

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?

...........