Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


MySQL 5.6 リファレンスマニュアル  /  関数と演算子  /  キャスト関数と演算子

12.10 キャスト関数と演算子

表 12.14 キャスト関数

名前 説明
BINARY 文字列をバイナリ文字列にキャストします
CAST() 値を特定の型としてキャストします
CONVERT() 値を特定の型としてキャストします

  • BINARY

    BINARY 演算子は、それに続く文字列をバイナリ文字列にキャストします。これは、カラムの比較を文字ごとでなく、バイトごとに強制的に実行させる簡単な方法です。これにより、カラムが BINARY または BLOB として定義されていない場合でも、比較で大文字と小文字が区別されます。また、BINARY では末尾の空白文字が重要になります。

    mysql> SELECT 'a' = 'A';
            -> 1
    mysql> SELECT BINARY 'a' = 'A';
            -> 0
    mysql> SELECT 'a' = 'a ';
            -> 1
    mysql> SELECT BINARY 'a' = 'a ';
            -> 0
    

    比較時に、BINARY によって演算全体が影響を受けます。これは、同じ結果を持ついずれかのオペランドの前に指定できます。

    BINARY str は、CAST(str AS BINARY) の略です。

    一部のコンテキストでは、インデックス付きのカラムを BINARY にキャストすると、MySQL はインデックスを効率的に使用できなくなります。

  • CAST(expr AS type)

    CONVERT() と同様に、CAST() 関数には任意の型の式が指定され、指定された型の結果値が生成されます。詳細は、CONVERT() の説明を参照してください。

  • CONVERT(expr,type), CONVERT(expr USING transcoding_name)

    CONVERT() および CAST() 関数には、任意の型の式が指定され、指定された型の結果値が生成されます。

    結果の type には、次の値のいずれかを指定できます。

    • BINARY[(N)]

    • CHAR[(N)]

    • DATE

    • DATETIME

    • DECIMAL[(M[,D])]

    • SIGNED [INTEGER]

    • TIME

    • UNSIGNED [INTEGER]

    BINARY は、BINARY データ型の文字列を生成します。これによる比較への影響度については、セクション11.4.2「BINARY および VARBINARY 型」を参照してください。オプションの長さ N が指定されている場合に、BINARY(N) を使用すると、キャストで N バイトの引数しか使用されなくなります。値が N バイトよりも短い場合は、N の長さになるまで 0x00 バイトでパディングされます。

    CHAR(N) 句を使用すると、キャストで N 文字の引数しか使用されなくなります。

    CAST() および CONVERT(... USING ...) は標準 SQL の構文です。USING 形式以外の CONVERT() は ODBC の構文です。

    USING とともに CONVERT() を使用すると、さまざまな文字セット間でデータが変換されます。MySQL では、トランスコーディング名は対応する文字セット名と同じです。たとえば、このステートメントは、デフォルトの文字セットの文字列 'abc'utf8 文字セットの対応する文字列に変換します。

    SELECT CONVERT('abc' USING utf8);
    

バイナリ文字列には文字セットがなく、大文字と小文字の区別の概念もないため、通常は、大文字と小文字が区別されない方法で、BLOB 値またはその他のバイナリ文字列を比較できません。大文字と小文字が区別されない比較を実行するには、CONVERT() 関数を使用して、値を非バイナリ文字列に変換します。結果の比較には、文字列照合順序が使用されます。たとえば、結果の文字セットに大文字と小文字が区別されない照合順序が含まれる場合は、LIKE 演算子でも大文字と小文字が区別されません。

SELECT 'A' LIKE CONVERT(blob_col USING latin1) FROM tbl_name;

別の文字セットを使用するには、上記のステートメントの latin1 にその名前を代入します。変換された文字列用に特定の照合順序を指定するには、セクション10.1.9.2「CONVERT() と CAST()」で説明するように、CONVERT() の呼び出しのあとに COLLATE 句を使用します。たとえば、latin1_german1_ci を使用する場合は次のとおりです。

SELECT 'A' LIKE CONVERT(blob_col USING latin1) COLLATE latin1_german1_ci
  FROM tbl_name;

CONVERT() は、さまざま文字セットで表される文字列を比較する際に、より一般的に使用できます。

LOWER() (および UPPER()) をバイナリ文字列 (BINARYVARBINARYBLOB) に適用しても、何の効果もありません。大文字/小文字の変換を実行するには、バイナリ文字列を非バイナリ文字列に変換します。

mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
+-------------+-----------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
+-------------+-----------------------------------+
| New York    | new york                          |
+-------------+-----------------------------------+

キャスト関数は、CREATE TABLE ... SELECT ステートメントで特定の型を持つカラムを作成する際に役立ちます。

CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);

この関数は、ENUM カラムを語彙順にソートする際に役立つこともあります。通常、ENUM カラムのソートは、内部数値を使用して実行されます。値を CHAR にキャストすると、語彙順にソートされます。

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST(str AS BINARY)BINARY str と同じです。CAST(expr AS CHAR) では、式はデフォルトの文字セットを持つ文字列として処理されます。

CONCAT('Date: ',CAST(NOW() AS DATE)) のように、より複雑な式の一部として使用する場合でも、CAST() の結果が変わります。

データを別の書式で抽出するには、CAST() ではなく、代わりに LEFT() または EXTRACT() のような文字列関数を使用してください。セクション12.7「日付および時間関数」を参照してください。

数値のコンテキストで文字列を数値にキャストするには、通常は、文字列値を数字と同様に使用する以外には何もする必要はありません。

mysql> SELECT 1+'1';
       -> 2

算術演算で文字列を使用する場合は、式の評価時に浮動小数点数に変換されます。

文字列のコンテキストで数字を使用する場合は、自動的に数字が文字列に変換されます。

mysql> SELECT CONCAT('hello you ',2);
        -> 'hello you 2'

MySQL 5.6.4 よりも前では、どのテーブルからも選択されないステートメントの TIMESTAMP 値で明示的に CAST() を使用すると、変換が実行される前に、その値が MySQL 5.6 で文字列として処理されます。これにより、値を数値型にキャストすると、次に示すように値が切り捨てられます。

mysql> SELECT CAST(TIMESTAMP '2014-09-08 18:07:54' AS SIGNED);
+-------------------------------------------------+
| CAST(TIMESTAMP '2014-09-08 18:07:54' AS SIGNED) |
+-------------------------------------------------+
|                                            2014 |
+-------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '2014-09-08 18:07:54' |
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

次に示すように、これはテーブルから行を選択するときには適用されません。

mysql> USE test;

Database changed
mysql> CREATE TABLE c_test (col TIMESTAMP);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO c_test VALUES ('2014-09-08 18:07:54');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT col, CAST(col AS UNSIGNED) AS c_col FROM c_test;
+---------------------+----------------+
| col                 | c_col          |
+---------------------+----------------+
| 2014-09-08 18:07:54 | 20140908180754 |
+---------------------+----------------+
1 row in set (0.00 sec)

MySQL 5.6.4 以降では、次に示すように、どの行も選択されていないクエリーでも、選択されている場合と同様に CAST() で TIMESTAMP 値が処理されます。

mysql> SELECT CAST(TIMESTAMP '2014-09-08 18:07:54' AS SIGNED);
+-------------------------------------------------+
| CAST(TIMESTAMP '2014-09-08 18:05:07' AS SIGNED) |
+-------------------------------------------------+
|                                  20140908180754 |
+-------------------------------------------------+
1 row in set (0.00 sec)

数字から文字列への暗黙的な変換については、セクション12.2「式評価での型変換」を参照してください。

MySQL では、符号付きと符号なしの両方の 64 ビット値を使用した演算がサポートされています。数値演算子 (+- など) を使用していて、オペランドのいずれかが符号なし整数である場合は、デフォルトの結果が符号なしになります (セクション12.6.1「算術演算子」を参照してください)。これは、SIGNED または UNSIGNED キャスト演算子を使用して、値をそれぞれ符号付きまたは符号なしの 64 ビット整数にキャストすることでオーバーライドできます。

mysql> SELECT CAST(1-2 AS UNSIGNED)
        -> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
        -> -1

オペランドのいずれかが浮動小数点値である場合は、結果が浮動小数点値になり、上記のルールによる影響を受けません。(このコンテキストでは、DECIMAL カラム値は浮動小数点値とみなされます。)

mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
        -> -1.0

SQL モードは、変換演算の結果に影響を与えます。例:

  • zero 日付文字列を日付に変換する場合、CONVERT()CAST()NULL を返し、NO_ZERO_DATE SQL モードが有効になると警告を発行します。

  • 整数の減算では、NO_UNSIGNED_SUBTRACTION SQL モードが有効になっている場合は、オペランドのいずれかが符号なしでも、結果が符号付きになります。

詳細は、セクション5.1.7「サーバー SQL モード」を参照してください。


User Comments
  Posted by anne blankert on May 29, 2006
To convert to numeric, the convert() and cast() functions are less forgiving then the implicit conversion when it comes to the data to be converted. If you want to convert "1a" or "1 apple", "2 apples", " 3 things" to 1, 1, 2 and 3 respectivly, the cast and convert function will produce an error. Instead use select 0+'1a', 0+'1 apple', 0+'2 apples', 0+' 3 things'.
  Posted by Ronald Rudy on December 5, 2006
Here's a workaround for not being able to cast/convert a value during table creation with just a create:

CREATE TEMPORARY TABLE tmp
SELECT 1000000.001-1000000.001 as n;

This will yield a table with the following structure:

+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| n | double(20,3) | | | 0.000 | |
+-------+--------------+------+-----+---------+-------+
  Posted by Mohamed Infiyaz Zaffer Khalid on January 4, 2008
The cast() function is amazingly useful when working with dates - not to mention date+time.

First, take a look at the following basic example:

select cast('2007-12-25' as DATETIME)

This naturally returns the output:
2007-12-25 00:00:00

But there are REALLY useful practical situations where we'd HAVE to use this function. Here's a case:

I want to pick all records from a table "Sales", where the "TransactionDate" field is between 25-Dec-2007 and 25-Jan-2008. Bear in mind that the field is of type DateTime.

Here's the BASIC query that I'd put in a string if I were to do it in PHP:

"SELECT * FROM Sales WHERE TransactionDate BETWEEN '$D1' and $D2"

In the above case, I'm assuming that $D1 and $D2 are posted variables in PHP. Unfortunately The above line won't work. And THIS is where we can use the CAST() function.

So here's the CORRECT STATEMENT:

"SELECT * FROM Sales WHERE TransactionDate BETWEEN CAST('$D1' as DATETIME) and CAST($D2 as DATETIME)"

This does the job without a flaw!
Happy programming,
Khalid

  Posted by Ice Fire on August 26, 2008
Alphanumeric strings automatically evaluate to 0 when used in a SELECT query to match against a column of type INT.

For example:

Table T1
n: INT
n_squared: INT,

n n_squared
0 0
1 1
2 4
3 9
4 16
...

Q1: SELECT * FROM T1 WHERE n_squared='ABC';
Q2: SELECT * FROM T1 WHERE n_squared='ABC123';

Both queries produce identical results:

n n_squared
0 0
  Posted by D'n Russler on June 3, 2009
To save others time in searching for something that wasn't immediately obvious to me...

A list of character sets is in 9.1.12. Character Sets and Collations That MySQL Supports, http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html

  Posted by Jon Lewis on February 24, 2010
Here's another workaround for the lack of data types: create your own conversion function. I am working with a DB (unfortunately) converted from and used by MS Access. All the primary keys are therefor of type INT(10) SIGNED. When UNIONing a constant integer (SELECT 0 AS Key) with a queried value the result is of the wrong type. So, since "CAST(0 AS INT(10))" is not an option I created my own:

CREATE FUNCTION `IntToInt10`(TheInt INT)
RETURNS INT(10) SIGNED -- here is the trick
DETERMINISTIC
BEGIN
RETURN TheInt;
END

Now, whenever I need to force a result to be of type int(10) I just run it through this function. I'm sure it would work for other types too (although a bit more involved if converting between two different classes of data such as numeric to alpha). Hope that helps.
  Posted by guillaume - on June 23, 2010
If you need to convert an id to a part of a string, the easiest way is to:

WHERE dst LIKE CONCAT("%", CAST(fs.nid as BINARY),"%")

  Posted by Darren Cassar on September 7, 2010
Note that if you need to set collation for a cast you will have to do it after you finish the cast expression as explained: http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;
  Posted by ZAky ke on March 17, 2011
How do I cast column value not a string?

SELECT CONVERT(_latin1city USING utf8) from users;

Where city is a column in users table.

I need to this statement for insert statement for another table.

  Posted by Chris Wagner on September 28, 2011
One of the glaring omissions in MySQL is the inability to convert between binary strings and integers and IP's. Here is a way to do it.

Binary string to integer:
conv(hex(binfield), 16, 10)

Binary string to IP:
inet_ntoa(conv(hex(binfield), 16, 10))

  Posted by Martin Lester on November 15, 2011
Convert HEX (string) to DECIMAL

SELECT CAST(X'01ABCDEF' AS DECIMAL);

Thought that would be in the basic examples.
  Posted by Eric Kent on December 23, 2011
The comment by anne blankert on May 29 2006 is not correct, at least with the current version. In a script, both of these will return the expect value of 1:
select cast(‘1a’ as unsigned);
select 0 + ‘1a’;
However, both generate a warning (1292 Truncated incorrect … value).
When casting a character value to a numeric, the character value must be a properly formed number representation. In a script you can simply ignore the warning, the script will return the expected value and continue. However, in a stored procedure, even if you handle the warning with a condition handler, the statement:
set iValue = 0 + ‘1a’;
will return null.
To extract the numeric part of a string, you need to create a function that will parse it using substr().
Sign Up Login You must be logged in to post a comment.