Australia photo & PC memo

COALESCE(param1, param2, ...)

最初にNULLでない値を返す。

CONCAT_WS(separator, str1, str2,...)

区切り文字付きの CONCAT（CONCAT With Separator）。separatorがNULLだとNULLを返すが、CONCATと違って、strXがNULLだとその文字列はスキップされる。

カラムタイプ DATEとDATETIME

変換しないでそのまま比較すると、結果がおかしい

JOIN句の比較とWhere句の比較は結果が異なる

select * from a left join b on a.id=b.id and b.id is null;
select * from a left join b on a.id=b.id where b.id is null;

テーブルの定義を表示するには

SHOW CREATE TABLE table_name;

SELECTで変数をセットするときLIMITに注意

select @val := col_a from a limit 0, 1;
とすると、なぜか@valには最後のcol_aが代入される

SELECTでWHERE句に変数を使用するとき宣言に注意

SELECT * FROM a WHERE @myVal IS NULL OR a.col = @myVal;
で、SET @myVal = NULL; が事前に有ると無いのでは結果が異なる

Unsigned subtraction

In some cases where an unsigned subtraction would have resulted in a signed integer, it now results in an unsigned integer.
For example, "SELECT column_int - 1 FROM t;" If column column_int is unsigned integer and value is 0, the result is 18446744073709551615, not -1.
To get the older behavior, use CAST(i - 1 AS SIGNED) to convert the expression result to a signed value.

DATE      3 bytes '1000-01-01' to '9999-12-31'
TIME      3 bytes '-838:59:59' to '838:59:59'*
DATETIME  8 bytes '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP 4 bytes '1970-01-01 00:00:00' to mid-year 2037
YEAR      1 byte  1901 to 2155 or (19)70 to (20)69

* TIME columns can be sued to represent elapsed time.

The date format corresponds to the ANSI SQL (ISO 8601) format. This can be changeable by using DATE_FORMAT() and TIME_FORMAT().
TIMESTAMP column has two special attributes, DEFAULT CUURENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP, and both are assigned if those are ommitted when the table is created (after version 4.1), also NOT NULL is default.
MySQL does not allow to use DEFAULT CURRENT_TIMESTAMP with one column and ON UPDATE CURRENT_TIMESTAMP with another. This is achievable by creating two TIMESTAMP columns defining one column, that shold hold the creation time, with DEFAULT 0 and explicitly set it to NULL when a new recored is inserted, and another column, that should hold the update time, with DEFAULT CURRENT_TIMESTAMP:

CREATE TABLE test (
   created  TIMESTAMP DEFAULT 0,
   modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   aaaaa    VARCHAR(10)
);

INSERT INTO test (created, aaaaa) VALUES (NULL, 'test');
UPDATE test SET aaaaa = 'test 2';

MySQL is possible to set the current time zone on a per-connection basis.
1. Signed hour/minute offset ('+hh:mm' or '-hh:mm')
2. Named time zone ('US/Eastern')
3. SYSTEM time zone

Examples:
SELECT @@global.time_zone, @@session.time_zone;
SET [SESSION] time_zone = '+00:00';
SELECT CONVERT_TZ('2007-08-02 12:00:00', '+01:00', '+03:00');

Eclipse SQL Explorer
Execute Query