1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
select CAST(1-2 AS UNSIGNED);
CAST(1-2 AS UNSIGNED)
18446744073709551615
select CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER);
CAST(CAST(1-2 AS UNSIGNED) AS SIGNED INTEGER)
-1
select CONVERT('-1',UNSIGNED);
CONVERT('-1',UNSIGNED)
18446744073709551615
select cast(-5 as unsigned) | 1, cast(-5 as unsigned) & -1;
cast(-5 as unsigned) | 1 cast(-5 as unsigned) & -1
18446744073709551611 18446744073709551611
select cast(-5 as unsigned) -1, cast(-5 as unsigned) + 1;
cast(-5 as unsigned) -1 cast(-5 as unsigned) + 1
18446744073709551610 18446744073709551612
select ~5, cast(~5 as signed);
~5 cast(~5 as signed)
18446744073709551610 -6
explain extended select ~5, cast(~5 as signed);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select high_priority ~(5) AS `~5`,cast(~(5) as signed) AS `cast(~5 as signed)`
select cast(5 as unsigned) -6.0;
cast(5 as unsigned) -6.0
-1.0
select cast("A" as binary) = "a", cast(BINARY "a" as CHAR) = "A";
cast("A" as binary) = "a" cast(BINARY "a" as CHAR) = "A"
0 1
select cast("2001-1-1" as DATE), cast("2001-1-1" as DATETIME);
cast("2001-1-1" as DATE) cast("2001-1-1" as DATETIME)
2001-01-01 2001-01-01 00:00:00
select cast("1:2:3" as TIME);
cast("1:2:3" as TIME)
01:02:03
set names binary;
select cast(_latin1'test' as char character set latin2);
cast(_latin1'test' as char character set latin2)
test
select cast(_koi8r'ÔÅÓÔ' as char character set cp1251);
cast(_koi8r'ÔÅÓÔ' as char character set cp1251)
òåñò
create table t1 select cast(_koi8r'ÔÅÓÔ' as char character set cp1251) as t;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`t` char(4) character set cp1251 NOT NULL default ''
) TYPE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select
cast(_latin1'ab' AS char) as c1,
cast(_latin1'a ' AS char) as c2,
cast(_latin1'abc' AS char(2)) as c3,
cast(_latin1'a ' AS char(2)) as c4,
cast(_latin1'a' AS char(2)) as c5;
c1 c2 c3 c4 c5
ab a ab a a
create table t1 select
cast(_latin1'ab' AS char) as c1,
cast(_latin1'a ' AS char) as c2,
cast(_latin1'abc' AS char(2)) as c3,
cast(_latin1'a ' AS char(2)) as c4,
cast(_latin1'a' AS char(2)) as c5;
select * from t1;
c1 c2 c3 c4 c5
ab a ab a a
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` char(2) binary NOT NULL default '',
`c2` char(2) binary NOT NULL default '',
`c3` char(2) binary NOT NULL default '',
`c4` char(2) binary NOT NULL default '',
`c5` char(2) binary NOT NULL default ''
) TYPE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select
cast(_koi8r'ÆÇ' AS nchar) as c1,
cast(_koi8r'Æ ' AS nchar) as c2,
cast(_koi8r'ÆÇÈ' AS nchar(2)) as c3,
cast(_koi8r'Æ ' AS nchar(2)) as c4,
cast(_koi8r'Æ' AS nchar(2)) as c5;
c1 c2 c3 c4 c5
фг ф фг ф ф
create table t1 select
cast(_koi8r'ÆÇ' AS nchar) as c1,
cast(_koi8r'Æ ' AS nchar) as c2,
cast(_koi8r'ÆÇÈ' AS nchar(2)) as c3,
cast(_koi8r'Æ ' AS nchar(2)) as c4,
cast(_koi8r'Æ' AS nchar(2)) as c5;
select * from t1;
c1 c2 c3 c4 c5
фг ф фг ф ф
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` char(2) character set utf8 NOT NULL default '',
`c2` char(2) character set utf8 NOT NULL default '',
`c3` char(2) character set utf8 NOT NULL default '',
`c4` char(2) character set utf8 NOT NULL default '',
`c5` char(2) character set utf8 NOT NULL default ''
) TYPE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select cast("2001-1-1" as date) = "2001-01-01";
cast("2001-1-1" as date) = "2001-01-01"
1
select cast("2001-1-1" as datetime) = "2001-01-01 00:00:00";
cast("2001-1-1" as datetime) = "2001-01-01 00:00:00"
1
select cast("1:2:3" as TIME) = "1:02:03";
cast("1:2:3" as TIME) = "1:02:03"
0
select cast(NULL as DATE);
cast(NULL as DATE)
NULL
select cast(NULL as BINARY);
cast(NULL as BINARY)
NULL
|
