Buy this Reference Manual in softcover from Barnes & Noble!
Функция CAST имеет следующий синтаксис:
CAST(expression AS type)
или
CONVERT(expression,type)
где аргумент type представляет один из типов:
Функция 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.
| Posted by on Saturday July 27 2002, @5:38am | [Delete] [Edit] |
You should try:
SELECT ...., RAND() AS r ORDER BY r
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
Be aware of Unix_Timestamp. It is in localtime!
So daylight savingtime sensitive and thus, it is
not continouos!
| Posted by [name withheld] on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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?
| Posted by Keith Tyler on Friday May 17 2002, @6:24am | [Delete] [Edit] |
For random ordering on output, try:
SELECT ... ORDER BY RAND()
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
*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
| Posted by on Wednesday December 18 2002, @5:27pm | [Delete] [Edit] |
for calculating ages, use following
statement:
YEAR(NOW())-YEAR(bdate)-
IF(MONTH(NOW())
| Posted by Tom McClure on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by [name withheld] on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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?!
| Posted by Luis Mazzoni on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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!!
| Posted by Peter on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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!
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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!
| Posted by on Wednesday July 31 2002, @10:47am | [Delete] [Edit] |
Say you have the year & week number (2002, 25)
Is there any functions you can get the Date of
Monday for that week ?
| Posted by [name withheld] on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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/
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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"
| Posted by Henric Andersson on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by Henric Andersson on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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
| Posted by [name withheld] on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by Jim Keller on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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)
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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).
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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
| Posted by on Friday May 17 2002, @6:24am | [Delete] [Edit] |
'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.
| Posted by [name withheld] on Friday May 17 2002, @6:24am | [Delete] [Edit] |
How to insert date and time value to the data
base and How to retrive date and time from
database
| Posted by [name withheld] on Friday May 17 2002, @6:24am | [Delete] [Edit] |
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.
| Posted by [name withheld] on Tuesday February 11 2003, @11:56am | [Delete] [Edit] |
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.