6.3.5 Функции приведения типов

Функция CAST имеет следующий синтаксис:

CAST(expression AS type)

или

CONVERT(expression,type)

где аргумент type представляет один из типов:

• BINARY
• DATE
• DATETIME
• SIGNED {INTEGER}
• TIME
• UNSIGNED {INTEGER}

Функция CAST() соответствует синтаксису ANSI SQL99, а функция CONVERT() - синтаксису ODBC.

Данная функция приведения типов используется главным образом для создания столбца конкретного типа с помощью команды CREATE ... SELECT:

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

Выражение CAST string AS BINARY эквивалентно BINARY string.

Для преобразования строки в числовую величину обычно не нужно ничего делать: просто используйте строку так, как будто это число:

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

MySQL поддерживает арифметические операции с 64-битовыми величинами - как со знаковыми, так и с беззнаковыми. Если используются числовые операции (такие как +) и один из операндов представлен в виде unsigned integer, то результат будет беззнаковым. Его можно переопределить, используя операторы приведения 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

Если в арифметической операции используется строка, то результат преобразуется в число с плавающей точкой.

Функции CAST() и CONVERT() были добавлены в MySQL 4.0.2.

В MySQL 4.0 была изменены правила обработки беззнаковых величин, чтобы обеспечить более полную поддержку величин типа BIGINT. Если код необходимо использовать и для MySQL 4.0, и для версии 3.23 (в которой функция CAST, скорее всего, не будет работать), то можно, применив следующий трюк, получить при вычитании двух беззнаковых целочисленных столбцов результат со знаком:

SELECT (unsigned_column_1+0.0)-(unsigned_column_2+0.0);

Идея состоит в том, что перед выполнением вычитания данные столбцы приводятся к типу с плавающей точкой.

Если возникнут проблемы со столбцами типа UNSIGNED в старых приложениях MySQL при переносе их на MySQL 4.0, можно использовать параметр --sql-mode=NO_UNSIGNED_SUBTRACTION при запуске mysqld. Однако следует учитывать, что при этом теряется возможность эффективного использования столбцов типа UNSIGNED BIGINT.

User Comments

You should try:

SELECT ...., RAND() AS r ORDER BY r

Be aware of Unix_Timestamp. It is in localtime!
So daylight savingtime sensitive and thus, it is
not continouos!

hiya....

i was trying to use SELECT * FROM table_name ORDER BY RAND(), and it didn't work, then I realized that my crappy host has MySQL 3.22.30.

then I tryed to do something like "SELECT column, column2, column3, RAND() AS random FROM table ORDER BY random DESC LIMIT 20;" it doesn't work either (actually works, but it's not sorted)

is there any work around?

For random ordering on output, try:
SELECT ... ORDER BY RAND()

*sigh*. It would be REALLY nice if there was a
function that returns the interval between two
dates easily. But if you're trying to calculate
a current age from a birthdate, you can use:

DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS
(bdate)), '%Y')+0

for calculating ages, use following
statement:
YEAR(NOW())-YEAR(bdate)-
IF(MONTH(NOW())<MONTH(bdate),1,
IF(DAYOFMOTH(NOW())<DAYOFMONTH(bdate),1,0))

ORDER BY supports a numerical column reference
arg, so SELECT col_1, col_2, col_3, RAND() ORDER
BY 4 will work around your problem nicely. Very
handy for sorting on nameless expression columns.

I want a field default on a TIMESTAMP column that is 2 weeks in the future. I tried this in the create:
[news_expiry_date TIMESTAMP NOT NULL default DATE_ADD( sysdate(), Interval 14 day ),]
but that doesn't work.

I've tried out all the suggestions about ORDER BY
RAND()/random...
I get different random numbers, but still it's not
sorted, so I always get the same results at the
end. Is there any solution for this problem?!

I have this query:
SELECT table1, MONTH(date_table)AS xx FROM DB
WHERE
condition GROUP BY xx;
when I ran this into an ASP (with ADODB
connection)I have no records; but if I try with
mySQLGUI v 1.7.5 the query work fine...
Thank u!!

ASP users: if you're getting empty recordset
returned when using +, add "OPTION=16384" to your
connectionstring, or check "Change Bigint to Int"
in the DSN manager!

Once again, everybody insists on making
everything harder than it should be.

If you are trying to match up a date - say you
want to automate a message announcing users'
birthdays - it is a non-date language construct
you need, and it is much shorter.

In PHP, my query looks like this:

SELECT username FROM login WHERE birthdate
LIKE '%-$month-$day'";

This assumes that you already have variables set
for $month and $day as I do, but if you don't,
then use the built-in MySQL date functions, %m, %
d, etc.

PHP and MySQL should be easy!

Say you have the year & week number (2002, 25)
Is there any functions you can get the Date of
Monday for that week ?

rand() works great for the given examples, but
doesn't work when you throw in a group by
clause:

select *
from product, category
where product.category_id =
category.category_id
group by category.category_id
order by rand()

I had hoped to get one random product in
each category. Instead, I get the same
product each time.
Tried fooling it by grouping by the
product.category_id as well.
Still such luck.
MySQL 3.23.41
YMMV

When doing a select on a float/decimal data
type, be AWARE that what you might think is
equal is not to mysql. Example, say you have a
datatype DECIMAL(12,31), then when you do an
equal to comparison, the number you pass in has
to have 31 decimal places it seems. Like if you
pass in 51.2, it would not return equal. But to
make this work, use ROUND(colname, 1) which will
round the number to 1 decimal place and then
your where condition will work.

Doesn't seem right since 51.2 is equal to
51.200000000000000....

I've also read that this happens on greater than
or less than statements too, so be careful.
http://www.geocrawler.com/archives/3/8/1999/5/0/2
173969/

To take the difference between two dates I use
these line of code in PHP program.

$sec1=exec("date -d \"2002-03-12 13:20:45\" +%s");
$sec2=exec("date -d \"2002-03-15 23:57:34\" +%s");

$diff=$sec1-$sec2;
$mm=$diff/60;
$hh=$mm/60;
$left_days=(int)($hh/24);

$left_hour=$hh%24;
$left_min=$mm%60;
$left_sec=$diff%60;

echo "$left_days Days,
$left_hour:$left_min:$left_sec"

The WEEK() function is broken! If you do this:

SELECT WEEK("2001-12-31", 1);

it returns 53, which is wrong, it should be 1. So if you intend to use this function for week numbering (as I was doing), I'm afraid you have to do manual checking in PHP (or whatever) to make sure it shows the correct value.

My bad, the WEEK() isn't wrong, it's just that it
doesn't use ISO Week Numbers (8601), but Absolute
Week Numbers (xxxx-01-01 is ALWAYS week 1).

For more info on this subject, check out:
http://www.cpearson.com/excel/weeknum.htm

For those who need to convert from (MM/DD/YYYY)
format to (YYYY/MM/DD the ansi standard used in
mysql) (it seems to have been rather rudely left
out) without using scripting languages to do so,
you can do this...

CONCAT( SUBSTRING_INDEX( MM-DD-YYYY-Date, '/', -
1) , '/', SUBSTRING_INDEX( MM-DD-YYYY-Date, '/',
1), '/', SUBSTRING_INDEX(SUBSTRING_INDEX(MM-DD-
YYYY-Date, '/',2), '/', -1))

Note, change the '/' to whatever deliminator you
are using in your date. This works well to
convert the date to the format that mysql will
understand and work properly with. To convert
from the ansi standard date format (YYYY/MM/DD)
back to (MM/DD/YYYY) use the DATE_FORMAT function.

The previous example for calculating
an age from a birthday does not work correctly.
This should work as expected, however it may be
better just to select the month, day and year and
do the arithmetic in your script rather than in
the query:

YEAR(NOW()) - YEAR(birthDate) - IF ( MONTH(NOW())
< MONTH(birthDate), 1, 0 ) - IF ( MONTH(NOW()) =
MONTH(birthDate) AND DAYOFMONTH(NOW()) <
DAYOFMONTH(birthDate), 1, 0)

I need a Integer field (mat_grupo) in SQL, but
not always I have this field available, then I
try to use:

CAST(0 as signed) as mat_grupo

to obtain it, but an error occurred after CAST
keyword. Where is the error? (excuse me by bad
English).

A very easy way to emulate date/time arithmetics
is to use the unix_timestamp() function, which
converts a mysql datetime to a unix timestamp
(number of seconds since 1970).

You can for instance compute the difference
between two dates by doing something like that:

select * from blah where ((unix_timestamp
(enddate)-unix_timestamp(startdate))/3600)>48;

this will select records where enddate-startdate
is more than 48 hours.

hope this helps..

-nd

'RAND() is not meant to be a perfect random
generator, but instead a fast way to generate
add-hook random numbers that will be portable
between platforms for the same MySQL version.'

I think you meant 'ad-hoc random numbers', mate.

How to insert date and time value to the data
base and How to retrive date and time from
database

So how do you change the precision of the result
of an expression... for example, when i divide two
INT's, it always rounds at the second place after
the decimal and i want it to the third place.

I am using MySQL 3.23 and thus don't have access to the CAST function. I have a string field that I wanted to be an integer. I got around this by using a simple calculation to force the result to be an integer. For example:

SELECT (string + 0) from examples;

Add your own comment.