Archives

You are currently viewing archive for 02 August 2007
Category: Web dev
Posted by: hajime osako
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.
Category: Study
Posted by: hajime osako
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');
Category: Web dev
Posted by: hajime osako