Alex Stetsenko

Just reminder that MySQL statement for selecting more than one field into variables should look like below:

SELECT ID, `Name` INTO UserID, UserName FROM `User` WHERE ID = 100;

I.e. single INTO per SELECT statement instead of own INTO for each field.

There is a good MySQL string function QUOTE(str) which quotes a string to produce a result that enclosed by single quotes and with each instance of single quote (“’”) or backslash (“\”) preceded by a backslash.

Here is detailed description and simple usage example. But I found this function very helpful in dynamic SQL where string variable is used:

SET @SQL = CONCAT('INSERT INTO SomeTable (Field0, Field1, Field2)
SELECT ', QUOTE(someStringVariable), ', Field1, Field2
FROM AnotherTable');
 
PREPARE query FROM @SQL;
EXECURE query;
DEALLOCATE PREPARE query;

Here using QOUTE(str) helps to avoid writing SQL like below (no worry about quotes):

SET @SQL = CONCAT('INSERT INTO SomeTable (Field0, Field1, Field2)
SELECT \'', someStringVariable, '\', Field1, Field2
FROM AnotherTable');

as well as SQL statement will not fail in case someStringVariable contains single quote or backslash.

The SQL Profiler is built into the database server and can be dynamically enabled/disabled via the MySQL client utility. To begin profiling one or more SQL queries, simply issue the following command:

SET profiling = 1;

Two things happen once you issue this command. First, any query you issue from this point on will be traced by the server with various performance diagnostics being created and attached to each distinct query. Second, a memory table named profiling is created in the INFORMATION_SCHEMA database for your particular session (not viewable by any other MySQL session) that stores all the SQL diagnostic results. This table remains persistent until you disconnect from MySQL at which point it is destroyed.

Read more here – Using the New MySQL Query Profiler.

I was always wondering what the difference between “quick” and “fast” is.

And it looks like:

• fast applies to things that move (e.g., “fast horses”, “a fast train”);
• quick means promptness and the taking of little time (e.g., “a quick dinner”, “quick thinking”).

Also there is another interesting example to understand the difference better:

• a quick question means that it does not take lots of time to ask and answer;
• a fast question refers to the talking speed of the enquirer.

Robin Schumacher, MySQL’s Director of Product Management overviews MySQL 6.0’s backup and restore utility.

MySQL 6.0’s backup/restore utility is a completely new tool in the server that allows for backup and restore commands to be issued right from a mysql client command prompt, which means no shelling out to an operating system prompt and/or scripting shell scripts to run a backup.

The syntax in the current alpha release looks like the following:

BACKUP {DATABASE | SCHEMA} { * | db_name [, db_name] ... }
TO 'image_file_name'
[WITH COMPRESSION [COMPRESSION_ALGORITHM [=] algorithm_name]];
 
RESTORE FROM 'image_file_name';

There is a session variable FOREIGN_KEY_CHECKS which allows to ignore checking of foreign key constraints for InnoDB tables.

If set to 1 (the default), foreign constraints are checked. If set to 0, they are ignored.
Setting FOREIGN_KEY_CHECKS to 0 also affects data definition statements like DROP TABLE which drops tables that have foreign keys that are referred to by other tables.

Disabling foreign key checking can be useful for reloading InnoDB tables in an order different from that required by their parent/child relationships.

Using session variables:

SET FOREIGN_KEY_CHECKS = 0;
 
SELECT @@FOREIGN_KEY_CHECKS;
 
SET FOREIGN_KEY_CHECKS = 1;

There is a simple way to find out current storage engine for any table(s) using SHOW TABLE STATUS query.

SHOW TABLE STATUS LIKE `User`;

Column Engine in result of query above shows current storage engine for table `User`.

SHOW TABLE STATUS LIKE `User%`;

Column Engine in result of query above (separate row for each table) shows current storage engine for tables like `User`, `UserPhoto`, `UserCommet` etc.

SHOW TABLE STATUS FROM `TestDB`;

Query above allows to find out storage engine for each table in database.

SHOW TABLE STATUS WHERE `Engine` = 'InnoDB';

And this query allows to get list of tables with storage engine InnoDB.

SQL query below helps to change table storage engine:

ALTER TABLE `User` ENGINE = MyISAM;

How can you select the minimum value in a row of data? This is quite tricky question for MS SQL but not for MySQL.
There are functions LEAST() and GREATEST() to get minimum and maximum value in a row.

For example a SQL query like below returns minimum of those three dates.

SELECT LEAST(DateCreated, DateChanged, DateUploaded)
FROM UserPhoto;

The same is for maximum:

SELECT GREATEST(DateCreated, DateChanged, DateUploaded)
FROM UserPhoto;

Draw attention that LEAST() (or GREATEST()) returns NULL if at least one input parameter is NULL. Thus one of possible workarounds is using of function IFNULL().for each input parameter.

This is a video with a woman who imitates 21 different English accents, including British, Irish, Scottish, Australian, and American accents, as well as several European accents.

Thanks to ESL Podcast Blog.

A temporary table could be very useful in some cases to keep temporary data.

There are couple interesting things about temporary tables.

1) A temporary table is visible only to the current connection, and is dropped automatically when the connection is closed.This means that two different connections can use the same temporary table name without conflicting with each other.

2) There is a short way to create a temporary table filled with data:

CREATE TEMPORARY TABLE MyTemporaryTable
SELECT ID, `Name` FROM MyRealTable WHERE ID < 10;

instead of more traditional way like:

CREATE TEMPORARY TABLE MyTemporaryTable
(
  ID INT,
  `Name` VARCHAR(100)
);
 
INSERT INTO MyTemporaryTable (ID, `Name`)
SELECT ID, `Name` FROM MyRealTable WHERE ID < 10;