'Syntax' Tag

• Merging tables with INSERT...ON DUPLICATE KEY UPDATE

  February 21, 2013

  Had a case recently where I had to merge data from two identically structured tables containing nearly identical data. "Nearly identical" meaning most table data is identical in both; sometimes a row is missing from one of the tables; sometimes same row (according to PK) appears in both, but some columns are NULL is one [...]

  posted in MySQL by shlomi

• Pop quiz: funny syntax

  November 5, 2012

  The following questions are of little importance, yet I find them entertaining. I stumbled upon these while developing QueryScript. Can you guess the results of the following statements? Pop quiz 1 SET @x := 7; SELECT ++@x; What is the computation result? What will be the value of @x? Pop quiz 2 SET @ := [...]

  posted in MySQL by shlomi

• MySQL joins: ON vs. USING vs. Theta-style

  July 11, 2012

  What is the difference between the following three syntaxes? SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) SELECT * FROM film JOIN film_actor USING (film_id) SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id The difference is mostly syntactic sugar, but with a couple interesting notes. To put names, the first two are [...]

  posted in MySQL by shlomi

• Syntax of the day: IS TRUE and IS FALSE

  January 26, 2012

  What makes for a true statement? We usually test statements using a WHERE clause: SELECT * FROM world.City WHERE Population > 1000000 The "Population > 1000000" statement makes for a boolean expression. Using WHERE is just one way of evaluating it. One can also test with IF(): SET @val := 7; SELECT IF(@val > 2, [...]

  posted in MySQL by shlomi

• Quoting text JavaScript/Python style

  November 15, 2011

  Unless your MySQL is configured to use ANSI_QUOTES in sql_mode, you are able to quote your text in one of two forms: using single quotes or double quotes: UPDATE world.Country SET HeadOfState = 'Willy Wonka' WHERE Code='USA' UPDATE world.Country SET HeadOfState = "Willy Wonka" WHERE Code="USA" This makes for JavaScript- or Python-style quoting: you quote [...]

  posted in MySQL by shlomi

• MySQL terminology: processes, threads & connections

  November 3, 2010

  There's some confusion in the MySQL terminology for processes, threads & connections, which I will try to resolve. I can't explain the full nature of what processes and threads are; please see Wikipedia [1] [2] for that. But here's some basics with regard to MySQL: MySQL server is a single process application. It is multithreaded. [...]

  posted in MySQL by shlomi

• SQL: good comments conventions

  July 1, 2010

  I happened upon a customer who left me in awe and admiration. The reason: excellent comments for their SQL code. I list four major places where SQL comments are helpful. I'll use the sakila database. It is originally scarcely commented; I'll present it now enhanced with comments, to illustrate. Table definitions The CREATE TABLE statement [...]

  posted in Development, MySQL by shlomi

• Views: better performance with condition pushdown

  May 20, 2010

  Justin's A workaround for the performance problems of TEMPTABLE views post on mysqlperformanceblog.com reminded me of a solution I once saw on a customer's site. The customer was using nested views structure, up to depth of some 8-9 views. There were a lot of aggregations along the way, and even the simplest query resulted with [...]

  posted in MySQL by shlomi

• Discovery of the day: GROUP BY ... DESC

  May 4, 2010

  I happened on a query where, by mistake, an was written as And it took me by surprise to realize GROUP BY x DESC is a valid statement. I looked it up: yep! It's documented. In MySQL, GROUP BY results are sorted according to the group statement. You can override this by adding ORDER BY [...]

  posted in MySQL by shlomi

• But I DO want MySQL to say "ERROR"!

  March 12, 2010

  MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data. Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though). Calling CREATE TEMPORARY TABLE? You get silent commit. Issuing a ROLLBACK on non-transactional involved [...]

  posted in MySQL by shlomi