drop table if exists t1,t2;
set names utf8;
select left(_utf8 0xD0B0D0B1D0B2,1);
left(_utf8 0xD0B0D0B1D0B2,1)
а
select right(_utf8 0xD0B0D0B2D0B2,1);
right(_utf8 0xD0B0D0B2D0B2,1)
в
select locate('he','hello');
locate('he','hello')
1
select locate('he','hello',2);
locate('he','hello',2)
0
select locate('lo','hello',2);
locate('lo','hello',2)
4
select locate('HE','hello');
locate('HE','hello')
1
select locate('HE','hello',2);
locate('HE','hello',2)
0
select locate('LO','hello',2);
locate('LO','hello',2)
4
select locate('HE','hello' collate utf8_bin);
locate('HE','hello' collate utf8_bin)
0
select locate('HE','hello' collate utf8_bin,2);
locate('HE','hello' collate utf8_bin,2)
0
select locate('LO','hello' collate utf8_bin,2);
locate('LO','hello' collate utf8_bin,2)
0
select locate(_utf8 0xD0B1, _utf8 0xD0B0D0B1D0B2);
locate(_utf8 0xD0B1, _utf8 0xD0B0D0B1D0B2)
2
select locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2);
locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2)
2
select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2);
locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2)
2
select locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2 collate utf8_bin);
locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2 collate utf8_bin)
0
select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2 collate utf8_bin);
locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2 collate utf8_bin)
0
select length(_utf8 0xD0B1), bit_length(_utf8 0xD0B1), char_length(_utf8 0xD0B1);
length(_utf8 0xD0B1) bit_length(_utf8 0xD0B1) char_length(_utf8 0xD0B1)
2 16 1
select 'a' like 'a';
'a' like 'a'
1
select 'A' like 'a';
'A' like 'a'
1
select 'A' like 'a' collate utf8_bin;
'A' like 'a' collate utf8_bin
0
select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%');
_utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%')
1
select convert(_latin1'G�nter Andr�' using utf8) like CONVERT(_latin1'G�NTER%' USING utf8);
convert(_latin1'G�nter Andr�' using utf8) like CONVERT(_latin1'G�NTER%' USING utf8)
1
select CONVERT(_koi8r'���' USING utf8) LIKE CONVERT(_koi8r'����' USING utf8);
CONVERT(_koi8r'���' USING utf8) LIKE CONVERT(_koi8r'����' USING utf8)
1
select CONVERT(_koi8r'����' USING utf8) LIKE CONVERT(_koi8r'���' USING utf8);
CONVERT(_koi8r'����' USING utf8) LIKE CONVERT(_koi8r'���' USING utf8)
1
SELECT 'a' = 'a ';
'a' = 'a '
1
SELECT 'a\0' < 'a';
'a\0' < 'a'
1
SELECT 'a\0' < 'a ';
'a\0' < 'a '
1
SELECT 'a\t' < 'a';
'a\t' < 'a'
1
SELECT 'a\t' < 'a ';
'a\t' < 'a '
1
SELECT 'a' = 'a ' collate utf8_bin;
'a' = 'a ' collate utf8_bin
1
SELECT 'a\0' < 'a' collate utf8_bin;
'a\0' < 'a' collate utf8_bin
1
SELECT 'a\0' < 'a ' collate utf8_bin;
'a\0' < 'a ' collate utf8_bin
1
SELECT 'a\t' < 'a' collate utf8_bin;
'a\t' < 'a' collate utf8_bin
1
SELECT 'a\t' < 'a ' collate utf8_bin;
'a\t' < 'a ' collate utf8_bin
1
CREATE TABLE t1 (a char(10) character set utf8 not null);
INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');
SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1;
hex(a) STRCMP(a,'a') STRCMP(a,'a ')
61 0 0
6100 -1 -1
6109 -1 -1
61 0 0
DROP TABLE t1;
select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
insert('txs',2,1,'hi') insert('is ',4,0,'a') insert('txxxxt',2,4,'es')
this is test
select insert("aa",100,1,"b"),insert("aa",1,3,"b");
insert("aa",100,1,"b") insert("aa",1,3,"b")
aa b
select char_length(left(@a:='тест',5)), length(@a), @a;
char_length(left(@a:='тест',5)) length(@a) @a
4 8 тест
create table t1 select date_format("2004-01-19 10:10:10", "%Y-%m-%d");
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`date_format("2004-01-19 10:10:10", "%Y-%m-%d")` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
date_format("2004-01-19 10:10:10", "%Y-%m-%d")
2004-01-19
drop table t1;
set names utf8;
set LC_TIME_NAMES='fr_FR';
create table t1 (s1 char(20) character set latin1);
insert into t1 values (date_format('2004-02-02','%M'));
select hex(s1) from t1;
hex(s1)
66E97672696572
drop table t1;
create table t1 (s1 char(20) character set koi8r);
set LC_TIME_NAMES='ru_RU';
insert into t1 values (date_format('2004-02-02','%M'));
insert into t1 values (date_format('2004-02-02','%b'));
insert into t1 values (date_format('2004-02-02','%W'));
insert into t1 values (date_format('2004-02-02','%a'));
select hex(s1), s1 from t1;
hex(s1) s1
E6C5D7D2C1CCD1 Февраля
E6C5D7 Фев
F0CFCEC5C4C5CCD8CEC9CB Понедельник
F0CEC4 Пнд
drop table t1;
set LC_TIME_NAMES='en_US';
set names koi8r;
create table t1 (s1 char(1) character set utf8);
insert into t1 values (_koi8r'��');
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
select s1,hex(s1),char_length(s1),octet_length(s1) from t1;
s1 hex(s1) char_length(s1) octet_length(s1)
� D0B0 1 2
drop table t1;
create table t1 (s1 tinytext character set utf8);
insert into t1 select repeat('a',300);
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
insert into t1 select repeat('�',300);
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
insert into t1 select repeat('a�',300);
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
insert into t1 select repeat('�a',300);
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
insert into t1 select repeat('��',300);
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
select hex(s1) from t1;
hex(s1)
616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161
D18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18F
61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F
D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61
D18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18F
select length(s1),char_length(s1) from t1;
length(s1) char_length(s1)
255 255
254 127
255 170
255 170
254 127
drop table t1;
create table t1 (s1 text character set utf8);
insert into t1 select repeat('a',66000);
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
insert into t1 select repeat('�',66000);
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
insert into t1 select repeat('a�',66000);
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
insert into t1 select repeat('�a',66000);
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
insert into t1 select repeat('��',66000);
Warnings:
Warning 1265 Data truncated for column 's1' at row 1
select length(s1),char_length(s1) from t1;
length(s1) char_length(s1)
65535 65535
65534 32767
65535 43690
65535 43690
65534 32767
drop table t1;
create table t1 (s1 char(10) character set utf8);
insert into t1 values (0x41FF);
Warnings:
Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1
select hex(s1) from t1;
hex(s1)
41
drop table t1;
create table t1 (s1 varchar(10) character set utf8);
insert into t1 values (0x41FF);
Warnings:
Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1
select hex(s1) from t1;
hex(s1)
41
drop table t1;
create table t1 (s1 text character set utf8);
insert into t1 values (0x41FF);
Warnings:
Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1
select hex(s1) from t1;
hex(s1)
41
drop table t1;
create table t1 (a text character set utf8, primary key(a(360)));
ERROR 42000: Specified key was too long; max key length is 1000 bytes
CREATE TABLE t1 ( a varchar(10) ) CHARACTER SET utf8;
INSERT INTO t1 VALUES ( 'test' );
SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a;
a a
test test
SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = 'test' and b.a = 'test';
a a
test test
SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a and a.a = 'test';
a a
test test
DROP TABLE t1;
create table t1 (a char(255) character set utf8);
insert into t1 values('b'),('b');
select * from t1 where a = 'b';
a
b
b
select * from t1 where a = 'b' and a = 'b';
a
b
b
select * from t1 where a = 'b' and a != 'b';
a
drop table t1;
set collation_connection=utf8_general_ci;
drop table if exists t1;
create table t1 as
select repeat(' ', 64) as s1, repeat(' ',64) as s2
union
select null, null;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`s1` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
`s2` varchar(64) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
delete from t1;
insert into t1 values('aaa','aaa');
insert into t1 values('aaa|qqq','qqq');
insert into t1 values('gheis','^[^a-dXYZ]+$');
insert into t1 values('aab','^aa?b');
insert into t1 values('Baaan','^Ba*n');
insert into t1 values('aaa','qqq|aaa');
insert into t1 values('qqq','qqq|aaa');
insert into t1 values('bbb','qqq|aaa');
insert into t1 values('bbb','qqq');
insert into t1 values('aaa','aba');
insert into t1 values(null,'abc');
insert into t1 values('def',null);
insert into t1 values(null,null);
insert into t1 values('ghi','ghi[');
select HIGH_PRIORITY s1 regexp s2 from t1;
s1 regexp s2
1
1
1
1
1
1
1
0
0
0
NULL
NULL
NULL
NULL
drop table t1;
set names utf8;
set names utf8;
select 'вася' rlike '[[:<:]]вася[[:>:]]';
'вася' rlike '[[:<:]]вася[[:>:]]'
1
select 'вася ' rlike '[[:<:]]вася[[:>:]]';
'вася ' rlike '[[:<:]]вася[[:>:]]'
1
select ' вася' rlike '[[:<:]]вася[[:>:]]';
' вася' rlike '[[:<:]]вася[[:>:]]'
1
select ' вася ' rlike '[[:<:]]вася[[:>:]]';
' вася ' rlike '[[:<:]]вася[[:>:]]'
1
select 'васяz' rlike '[[:<:]]вася[[:>:]]';
'васяz' rlike '[[:<:]]вася[[:>:]]'
0
select 'zвася' rlike '[[:<:]]вася[[:>:]]';
'zвася' rlike '[[:<:]]вася[[:>:]]'
0
select 'zвасяz' rlike '[[:<:]]вася[[:>:]]';
'zвасяz' rlike '[[:<:]]вася[[:>:]]'
0
CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8_unicode_ci);
ALTER TABLE t1 ADD COLUMN b CHAR(20);
DROP TABLE t1;
set names utf8;
create table t1 (a enum('aaaa','проба') character set utf8);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` enum('aaaa','проба') CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ('проба');
select * from t1;
a
проба
create table t2 select ifnull(a,a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`ifnull(a,a)` varchar(5) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t2;
ifnull(a,a)
проба
drop table t1;
drop table t2;
create table t1 (c varchar(30) character set utf8, unique(c(10)));
insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');
insert into t1 values ('aaaaaaaaaa');
insert into t1 values ('aaaaaaaaaaa');
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
insert into t1 values ('aaaaaaaaaaaa');
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
insert into t1 values (repeat('b',20));
select c c1 from t1 where c='1';
c1
1
select c c2 from t1 where c='2';
c2
2
select c c3 from t1 where c='3';
c3
3
select c cx from t1 where c='x';
cx
x
select c cy from t1 where c='y';
cy
y
select c cz from t1 where c='z';
cz
z
select c ca10 from t1 where c='aaaaaaaaaa';
ca10
aaaaaaaaaa
select c cb20 from t1 where c=repeat('b',20);
cb20
bbbbbbbbbbbbbbbbbbbb
drop table t1;
create table t1 (c varchar(30) character set utf8, unique(c(10))) engine=innodb;
insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');
insert into t1 values ('aaaaaaaaaa');
insert into t1 values ('aaaaaaaaaaa');
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
insert into t1 values ('aaaaaaaaaaaa');
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
insert into t1 values (repeat('b',20));
select c c1 from t1 where c='1';
c1
1
select c c2 from t1 where c='2';
c2
2
select c c3 from t1 where c='3';
c3
3
select c cx from t1 where c='x';
cx
x
select c cy from t1 where c='y';
cy
y
select c cz from t1 where c='z';
cz
z
select c ca10 from t1 where c='aaaaaaaaaa';
ca10
aaaaaaaaaa
select c cb20 from t1 where c=repeat('b',20);
cb20
bbbbbbbbbbbbbbbbbbbb
drop table t1;
create table t1 (c char(3) character set utf8, unique (c(2)));
insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');
insert into t1 values ('a');
insert into t1 values ('aa');
insert into t1 values ('aaa');
ERROR 23000: Duplicate entry 'aa' for key 'c'
insert into t1 values ('b');
insert into t1 values ('bb');
insert into t1 values ('bbb');
ERROR 23000: Duplicate entry 'bb' for key 'c'
insert into t1 values ('а');
insert into t1 values ('аа');
insert into t1 values ('ааа');
ERROR 23000: Duplicate entry 'аа' for key 'c'
insert into t1 values ('б');
insert into t1 values ('бб');
insert into t1 values ('ббб');
ERROR 23000: Duplicate entry 'бб' for key 'c'
insert into t1 values ('ꪪ');
insert into t1 values ('ꪪꪪ');
insert into t1 values ('ꪪꪪꪪ');
ERROR 23000: Duplicate entry 'ꪪꪪ' for key 'c'
drop table t1;
create table t1 (c char(3) character set utf8, unique (c(2))) engine=innodb;
insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');
insert into t1 values ('a');
insert into t1 values ('aa');
insert into t1 values ('aaa');
ERROR 23000: Duplicate entry 'aa' for key 'c'
insert into t1 values ('b');
insert into t1 values ('bb');
insert into t1 values ('bbb');
ERROR 23000: Duplicate entry 'bb' for key 'c'
insert into t1 values ('а');
insert into t1 values ('аа');
insert into t1 values ('ааа');
ERROR 23000: Duplicate entry 'аа' for key 'c'
insert into t1 values ('б');
insert into t1 values ('бб');
insert into t1 values ('ббб');
ERROR 23000: Duplicate entry 'бб' for key 'c'
insert into t1 values ('ꪪ');
insert into t1 values ('ꪪꪪ');
insert into t1 values ('ꪪꪪꪪ');
ERROR 23000: Duplicate entry 'ꪪꪪ' for key 'c'
drop table t1;
create table t1 (
c char(10) character set utf8,
unique key a using hash (c(1))
) engine=heap;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` char(10) CHARACTER SET utf8 DEFAULT NULL,
UNIQUE KEY `a` (`c`(1)) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=latin1
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
insert into t1 values ('aa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('aaa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('б');
insert into t1 values ('бб');
ERROR 23000: Duplicate entry 'б' for key 'a'
insert into t1 values ('ббб');
ERROR 23000: Duplicate entry 'б' for key 'a'
select c as c_all from t1 order by c;
c_all
a
b
c
d
e
f
б
select c as c_a from t1 where c='a';
c_a
a
select c as c_a from t1 where c='б';
c_a
б
drop table t1;
create table t1 (
c char(10) character set utf8,
unique key a using btree (c(1))
) engine=heap;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` char(10) CHARACTER SET utf8 DEFAULT NULL,
UNIQUE KEY `a` (`c`(1)) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=latin1
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
insert into t1 values ('aa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('aaa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('б');
insert into t1 values ('бб');
ERROR 23000: Duplicate entry 'б' for key 'a'
insert into t1 values ('ббб');
ERROR 23000: Duplicate entry 'б' for key 'a'
select c as c_all from t1 order by c;
c_all
a
b
c
d
e
f
б
select c as c_a from t1 where c='a';
c_a
a
select c as c_a from t1 where c='б';
c_a
б
drop table t1;
create table t1 (
c char(10) character set utf8,
unique key a (c(1))
) engine=innodb;
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
insert into t1 values ('aa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('aaa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('б');
insert into t1 values ('бб');
ERROR 23000: Duplicate entry 'б' for key 'a'
insert into t1 values ('ббб');
ERROR 23000: Duplicate entry 'б' for key 'a'
select c as c_all from t1 order by c;
c_all
a
b
c
d
e
f
б
select c as c_a from t1 where c='a';
c_a
a
select c as c_a from t1 where c='б';
c_a
б
drop table t1;
create table t1 (c varchar(30) character set utf8 collate utf8_bin, unique(c(10)));
insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');
insert into t1 values ('aaaaaaaaaa');
insert into t1 values ('aaaaaaaaaaa');
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
insert into t1 values ('aaaaaaaaaaaa');
ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'
insert into t1 values (repeat('b',20));
select c c1 from t1 where c='1';
c1
1
select c c2 from t1 where c='2';
c2
2
select c c3 from t1 where c='3';
c3
3
select c cx from t1 where c='x';
cx
x
select c cy from t1 where c='y';
cy
y
select c cz from t1 where c='z';
cz
z
select c ca10 from t1 where c='aaaaaaaaaa';
ca10
aaaaaaaaaa
select c cb20 from t1 where c=repeat('b',20);
cb20
bbbbbbbbbbbbbbbbbbbb
drop table t1;
create table t1 (c char(3) character set utf8 collate utf8_bin, unique (c(2)));
insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');
insert into t1 values ('a');
insert into t1 values ('aa');
insert into t1 values ('aaa');
ERROR 23000: Duplicate entry 'aa' for key 'c'
insert into t1 values ('b');
insert into t1 values ('bb');
insert into t1 values ('bbb');
ERROR 23000: Duplicate entry 'bb' for key 'c'
insert into t1 values ('а');
insert into t1 values ('аа');
insert into t1 values ('ааа');
ERROR 23000: Duplicate entry 'аа' for key 'c'
insert into t1 values ('б');
insert into t1 values ('бб');
insert into t1 values ('ббб');
ERROR 23000: Duplicate entry 'бб' for key 'c'
insert into t1 values ('ꪪ');
insert into t1 values ('ꪪꪪ');
insert into t1 values ('ꪪꪪꪪ');
ERROR 23000: Duplicate entry 'ꪪꪪ' for key 'c'
drop table t1;
create table t1 (
c char(10) character set utf8 collate utf8_bin,
unique key a using hash (c(1))
) engine=heap;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` char(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
UNIQUE KEY `a` (`c`(1)) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=latin1
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
insert into t1 values ('aa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('aaa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('б');
insert into t1 values ('бб');
ERROR 23000: Duplicate entry 'б' for key 'a'
insert into t1 values ('ббб');
ERROR 23000: Duplicate entry 'б' for key 'a'
select c as c_all from t1 order by c;
c_all
a
b
c
d
e
f
б
select c as c_a from t1 where c='a';
c_a
a
select c as c_a from t1 where c='б';
c_a
б
drop table t1;
create table t1 (
c char(10) character set utf8 collate utf8_bin,
unique key a using btree (c(1))
) engine=heap;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` char(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
UNIQUE KEY `a` (`c`(1)) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=latin1
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
insert into t1 values ('aa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('aaa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('б');
insert into t1 values ('бб');
ERROR 23000: Duplicate entry 'б' for key 'a'
insert into t1 values ('ббб');
ERROR 23000: Duplicate entry 'б' for key 'a'
select c as c_all from t1 order by c;
c_all
a
b
c
d
e
f
б
select c as c_a from t1 where c='a';
c_a
a
select c as c_a from t1 where c='б';
c_a
б
drop table t1;
create table t1 (
c char(10) character set utf8 collate utf8_bin,
unique key a (c(1))
) engine=innodb;
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
insert into t1 values ('aa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('aaa');
ERROR 23000: Duplicate entry 'a' for key 'a'
insert into t1 values ('б');
insert into t1 values ('бб');
ERROR 23000: Duplicate entry 'б' for key 'a'
insert into t1 values ('ббб');
ERROR 23000: Duplicate entry 'б' for key 'a'
select c as c_all from t1 order by c;
c_all
a
b
c
d
e
f
б
select c as c_a from t1 where c='a';
c_a
a
select c as c_a from t1 where c='б';
c_a
б
drop table t1;
create table t1 (
str varchar(255) character set utf8 not null,
key str (str(2))
) engine=myisam;
INSERT INTO t1 VALUES ('str');
INSERT INTO t1 VALUES ('str2');
select * from t1 where str='str';
str
str
drop table t1;
create table t1 (
str varchar(255) character set utf8 not null,
key str (str(2))
) engine=innodb;
INSERT INTO t1 VALUES ('str');
INSERT INTO t1 VALUES ('str2');
select * from t1 where str='str';
str
str
drop table t1;
create table t1 (
str varchar(255) character set utf8 not null,
key str using btree (str(2))
) engine=heap;
INSERT INTO t1 VALUES ('str');
INSERT INTO t1 VALUES ('str2');
select * from t1 where str='str';
str
str
drop table t1;
create table t1 (
str varchar(255) character set utf8 not null,
key str using hash (str(2))
) engine=heap;
INSERT INTO t1 VALUES ('str');
INSERT INTO t1 VALUES ('str2');
select * from t1 where str='str';
str
str
drop table t1;
create table t1 (
str varchar(255) character set utf8 not null,
key str (str(2))
) engine=innodb;
INSERT INTO t1 VALUES ('str');
INSERT INTO t1 VALUES ('str2');
select * from t1 where str='str';
str
str
drop table t1;
CREATE TABLE t1 (a varchar(32) BINARY) CHARACTER SET utf8;
INSERT INTO t1 VALUES ('test');
SELECT a FROM t1 WHERE a LIKE '%te';
a
DROP TABLE t1;
SET NAMES utf8;
CREATE TABLE t1 (
subject varchar(255) character set utf8 collate utf8_unicode_ci,
p varchar(15) character set utf8
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES ('谷川俊二と申しますが、インターネット予約の会員登録をしましたところ、メールアドレスを間違えてしまい会員ＩＤが受け取ることが出来ませんでした。間違えアドレスはtani-shun@n.vodafone.ne.jpを書き込みました。どうすればよいですか？ その他、住所等は間違えありません。連絡ください。よろしくお願いします。m(__)m','040312-000057');
INSERT INTO t1 VALUES ('aaa','bbb');
SELECT length(subject) FROM t1;
length(subject)
432
3
SELECT length(subject) FROM t1 ORDER BY 1;
length(subject)
3
432
DROP TABLE t1;
CREATE TABLE t1 (
id int unsigned NOT NULL auto_increment,
list_id smallint unsigned NOT NULL,
term TEXT NOT NULL,
PRIMARY KEY(id),
INDEX(list_id, term(4))
) ENGINE=MYISAM CHARSET=utf8;
INSERT INTO t1 SET list_id = 1, term = "letterc";
INSERT INTO t1 SET list_id = 1, term = "letterb";
INSERT INTO t1 SET list_id = 1, term = "lettera";
INSERT INTO t1 SET list_id = 1, term = "letterd";
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc");
id
1
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb");
id
2
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");
id
3
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");
id
4
DROP TABLE t1;
SET NAMES latin1;
CREATE TABLE t1 (
id int unsigned NOT NULL auto_increment,
list_id smallint unsigned NOT NULL,
term text NOT NULL,
PRIMARY KEY(id),
INDEX(list_id, term(19))
) ENGINE=MyISAM CHARSET=utf8;
INSERT INTO t1 set list_id = 1, term = "test�test";
INSERT INTO t1 set list_id = 1, term = "testetest";
INSERT INTO t1 set list_id = 1, term = "test�test";
SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test");
id term
1 test�test
2 testetest
3 test�test
SELECT id, term FROM t1 where (list_id = 1) AND (term = "testetest");
id term
1 test�test
2 testetest
3 test�test
SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test");
id term
1 test�test
2 testetest
3 test�test
DROP TABLE t1;
set names utf8;
create table t1 (
a int primary key,
b varchar(6),
index b3(b(3))
) engine=innodb character set=utf8;
insert into t1 values(1,'foo'),(2,'foobar');
select * from t1 where b like 'foob%';
a b
2 foobar
alter table t1 engine=innodb;
select * from t1 where b like 'foob%';
a b
2 foobar
drop table t1;
create table t1 (
a enum('петя','вася','анюта') character set utf8 not null default 'анюта',
b set('петя','вася','анюта') character set utf8 not null default 'анюта'
);
create table t2 select concat(a,_utf8'') as a, concat(b,_utf8'')as b from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` varchar(5) CHARACTER SET utf8 NOT NULL DEFAULT '',
`b` varchar(15) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t2;
drop table t1;
select 'c' like '\_' as want0;
want0
0
SELECT SUBSTR('вася',-2);
SUBSTR('вася',-2)
ся
create table t1 (id integer, a varchar(100) character set utf8 collate utf8_unicode_ci);
insert into t1 values (1, 'Test');
select * from t1 where soundex(a) = soundex('Test');
id a
1 Test
select * from t1 where soundex(a) = soundex('TEST');
id a
1 Test
select * from t1 where soundex(a) = soundex('test');
id a
1 Test
drop table t1;
select soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB);
soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB)
阅000
select hex(soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB));
hex(soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB))
E99885303030
select soundex(_utf8 0xD091D092D093);
soundex(_utf8 0xD091D092D093)
Б000
select hex(soundex(_utf8 0xD091D092D093));
hex(soundex(_utf8 0xD091D092D093))
D091303030
SET collation_connection='utf8_general_ci';
create table t1 select repeat('a',4000) a;
delete from t1;
insert into t1 values ('a'), ('a '), ('a\t');
select collation(a),hex(a) from t1 order by a;
collation(a) hex(a)
utf8_general_ci 6109
utf8_general_ci 61
utf8_general_ci 6120
drop table t1;
select @@collation_connection;
@@collation_connection
utf8_general_ci
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
insert into t1 values('abcdef');
insert into t1 values('_bcdef');
insert into t1 values('a_cdef');
insert into t1 values('ab_def');
insert into t1 values('abc_ef');
insert into t1 values('abcd_f');
insert into t1 values('abcde_');
select c1 as c1u from t1 where c1 like 'ab\_def';
c1u
ab_def
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
c2h
ab_def
drop table t1;
"BEGIN ctype_german.inc"
drop table if exists t1;
create table t1 as select repeat(' ', 64) as s1;
select collation(s1) from t1;
collation(s1)
utf8_general_ci
delete from t1;
INSERT INTO t1 VALUES ('ud'),('uf');
INSERT INTO t1 VALUES ('od'),('of');
INSERT INTO t1 VALUES ('e');
INSERT INTO t1 VALUES ('ad'),('af');
insert into t1 values ('a'),('ae'),(_latin1 0xE4);
insert into t1 values ('o'),('oe'),(_latin1 0xF6);
insert into t1 values ('s'),('ss'),(_latin1 0xDF);
insert into t1 values ('u'),('ue'),(_latin1 0xFC);
INSERT INTO t1 VALUES (_latin1 0xE6), (_latin1 0xC6);
INSERT INTO t1 VALUES (_latin1 0x9C), (_latin1 0x8C);
select s1, hex(s1) from t1 order by s1, binary s1;
s1 hex(s1)
a 61
ä C3A4
ad 6164
ae 6165
af 6166
e 65
o 6F
ö C3B6
od 6F64
oe 6F65
of 6F66
s 73
ß C39F
ss 7373
u 75
ü C3BC
ud 7564
ue 7565
uf 7566
Æ C386
æ C3A6
ΠC592
œ C593
select group_concat(s1 order by binary s1) from t1 group by s1;
group_concat(s1 order by binary s1)
a,ä
ad
ae
af
e
o,ö
od
oe
of
s,ß
ss
u,ü
ud
ue
uf
Æ,æ
Œ,œ
SELECT s1, hex(s1), hex(weight_string(s1)) FROM t1 ORDER BY s1, BINARY(s1);
s1 hex(s1) hex(weight_string(s1))
a 61 0041
ä C3A4 0041
ad 6164 00410044
ae 6165 00410045
af 6166 00410046
e 65 0045
o 6F 004F
ö C3B6 004F
od 6F64 004F0044
oe 6F65 004F0045
of 6F66 004F0046
s 73 0053
ß C39F 0053
ss 7373 00530053
u 75 0055
ü C3BC 0055
ud 7564 00550044
ue 7565 00550045
uf 7566 00550046
Æ C386 00C6
æ C3A6 00C6
ΠC592 0152
œ C593 0152
SELECT s1, hex(s1) FROM t1 WHERE s1='ae' ORDER BY s1, BINARY(s1);
s1 hex(s1)
ae 6165
drop table t1;
"END ctype_german.inc"
SET collation_connection='utf8_bin';
create table t1 select repeat('a',4000) a;
delete from t1;
insert into t1 values ('a'), ('a '), ('a\t');
select collation(a),hex(a) from t1 order by a;
collation(a) hex(a)
utf8_bin 6109
utf8_bin 61
utf8_bin 6120
drop table t1;
select @@collation_connection;
@@collation_connection
utf8_bin
create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;
insert into t1 values('abcdef');
insert into t1 values('_bcdef');
insert into t1 values('a_cdef');
insert into t1 values('ab_def');
insert into t1 values('abc_ef');
insert into t1 values('abcd_f');
insert into t1 values('abcde_');
select c1 as c1u from t1 where c1 like 'ab\_def';
c1u
ab_def
select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';
c2h
ab_def
drop table t1;
CREATE TABLE t1 (
user varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES ('one'),('two');
SELECT CHARSET('a');
CHARSET('a')
utf8
SELECT user, CONCAT('<', user, '>') AS c FROM t1;
user c
one <one>
two <two>
DROP TABLE t1;
create table t1 (f1 varchar(1) not null) default charset utf8;
insert into t1 values (''), ('');
select concat(concat(_latin1'->',f1),_latin1'<-') from t1;
concat(concat(_latin1'->',f1),_latin1'<-')
-><-
-><-
drop table t1;
select convert(_koi8r'�' using utf8) < convert(_koi8r'�' using utf8);
convert(_koi8r'�' using utf8) < convert(_koi8r'�' using utf8)
1
set names latin1;
create table t1 (a varchar(10)) character set utf8;
insert into t1 values ('test');
select ifnull(a,'') from t1;
ifnull(a,'')
test
drop table t1;
select repeat(_utf8'+',3) as h union select NULL;
h
+++
NULL
select ifnull(NULL, _utf8'string');
ifnull(NULL, _utf8'string')
string
set names utf8;
create table t1 (s1 char(5) character set utf8 collate utf8_lithuanian_ci);
insert into t1 values ('I'),('K'),('Y');
select * from t1 where s1 < 'K' and s1 = 'Y';
s1
I
Y
select * from t1 where 'K' > s1 and s1 = 'Y';
s1
I
Y
drop table t1;
create table t1 (s1 char(5) character set utf8 collate utf8_czech_ci);
insert into t1 values ('c'),('d'),('h'),('ch'),('CH'),('cH'),('Ch'),('i');
select * from t1 where s1 > 'd' and s1 = 'CH';
s1
ch
CH
Ch
select * from t1 where 'd' < s1 and s1 = 'CH';
s1
ch
CH
Ch
select * from t1 where s1 = 'cH' and s1 <> 'ch';
s1
cH
select * from t1 where 'cH' = s1 and s1 <> 'ch';
s1
cH
drop table t1;
create table t1 (a varchar(255)) default character set utf8;
insert into t1 values (1.0);
drop table t1;
create table t1 (
id int not null,
city varchar(20) not null,
key (city(7),id)
) character set=utf8;
insert into t1 values (1,'Durban North');
insert into t1 values (2,'Durban');
select * from t1 where city = 'Durban';
id city
2 Durban
select * from t1 where city = 'Durban ';
id city
2 Durban
drop table t1;
create table t1 (x set('A', 'B') default 0) character set utf8;
ERROR 42000: Invalid default value for 'x'
create table t1 (x enum('A', 'B') default 0) character set utf8;
ERROR 42000: Invalid default value for 'x'
SET NAMES UTF8;
CREATE TABLE t1 (
`id` int(20) NOT NULL auto_increment,
`country` varchar(100) NOT NULL default '',
`shortcode` varchar(100) NOT NULL default '',
`operator` varchar(100) NOT NULL default '',
`momid` varchar(30) NOT NULL default '',
`keyword` varchar(160) NOT NULL default '',
`content` varchar(160) NOT NULL default '',
`second_token` varchar(160) default NULL,
`gateway_id` int(11) NOT NULL default '0',
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`msisdn` varchar(15) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `MSCCSPK_20030521130957121` (`momid`),
KEY `IX_mobile_originated_message_keyword` (`keyword`),
KEY `IX_mobile_originated_message_created` (`created`),
KEY `IX_mobile_originated_message_support` (`msisdn`,`momid`,`keyword`,`gateway_id`,`created`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES
(1,'blah','464','aaa','fkc1c9ilc20x0hgae7lx6j09','ERR','ERR Имри.Афимим.Аеимимримдмримрмрирор имримримримр имридм ирбднримрфмририримрфмфмим.Ад.Д имдимримрад.Адимримримрмдиримримримр м.Дадимфшьмримд им.Адимимрн имадми','ИМРИ.АФИМИМ.АЕИМИМРИМДМРИМРМРИРОР',3,'2005-06-01 17:30:43','1234567890'),
(2,'blah','464','aaa','haxpl2ilc20x00bj4tt2m5ti','11','11 g','G',3,'2005-06-02 22:43:10','1234567890');
CREATE TABLE t2 (
`msisdn` varchar(15) NOT NULL default '',
`operator_id` int(11) NOT NULL default '0',
`created` datetime NOT NULL default '0000-00-00 00:00:00',
UNIQUE KEY `PK_user` (`msisdn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t2 VALUES ('1234567890',2,'2005-05-24 13:53:25');
SELECT content, t2.msisdn FROM t1, t2 WHERE t1.msisdn = '1234567890';
content msisdn
ERR Имри.Афимим.Аеимимримдмримрмрирор имримримримр имридм ирбднримрфмририримрфмфмим.Ад.Д имдимримрад.Адимримримрмдиримримримр м.Дадимфшьмримд им.Адимимрн имадми 1234567890
11 g 1234567890
DROP TABLE t1,t2;
create table t1 (a char(20) character set utf8);
insert into t1 values ('123456'),('андрей');
alter table t1 modify a char(2) character set utf8;
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
Warning 1265 Data truncated for column 'a' at row 2
select char_length(a), length(a), a from t1 order by a;
char_length(a) length(a) a
2 2 12
2 4 ан
drop table t1;
set names utf8;
select 'andre%' like 'andreñ%' escape 'ñ';
'andre%' like 'andreñ%' escape 'ñ'
1
set names utf8;
select 'a\\' like 'a\\';
'a\\' like 'a\\'
1
select 'aa\\' like 'a%\\';
'aa\\' like 'a%\\'
1
create table t1 (a char(10), key(a)) character set utf8;
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
select * from t1 where a like "abc%";
a
abc
abcd
select * from t1 where a like concat("abc","%");
a
abc
abcd
select * from t1 where a like "ABC%";
a
abc
abcd
select * from t1 where a like "test%";
a
test
select * from t1 where a like "te_t";
a
test
select * from t1 where a like "%a%";
a
a
abc
abcd
select * from t1 where a like "%abcd%";
a
abcd
select * from t1 where a like "%abc\d%";
a
abcd
drop table t1;
CREATE TABLE t1 (
a varchar(255) NOT NULL default '',
KEY a (a)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
insert into t1 values (_utf8 0xe880bd);
insert into t1 values (_utf8 0x5b);
select hex(a) from t1;
hex(a)
5B
E880BD
drop table t1;
set names 'latin1';
create table t1 (a varchar(255)) default charset=utf8;
select * from t1 where find_in_set('-1', a);
a
drop table t1;
create table t1 (a int);
insert into t1 values (48),(49),(50);
set names utf8;
select distinct char(a) from t1;
char(a)
0
1
2
drop table t1;
CREATE TABLE t1 (t TINYTEXT CHARACTER SET utf8);
INSERT INTO t1 VALUES(REPEAT('a', 100));
CREATE TEMPORARY TABLE t2 SELECT COALESCE(t) AS bug FROM t1;
SELECT LENGTH(bug) FROM t2;
LENGTH(bug)
100
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1 (item varchar(255)) default character set utf8;
INSERT INTO t1 VALUES (N'\\');
INSERT INTO t1 VALUES (_utf8'\\');
INSERT INTO t1 VALUES (N'Cote d\'Ivoire');
INSERT INTO t1 VALUES (_utf8'Cote d\'Ivoire');
SELECT item FROM t1 ORDER BY item;
item
Cote d'Ivoire
Cote d'Ivoire
\
\
DROP TABLE t1;
SET NAMES utf8;
DROP TABLE IF EXISTS t1;
Warnings:
Note 1051 Unknown table 'test.t1'
CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
INSERT INTO t1 VALUES('uu');
check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
INSERT INTO t1 VALUES('uU');
check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
INSERT INTO t1 VALUES('uu');
check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
INSERT INTO t1 VALUES('uuABC');
check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
INSERT INTO t1 VALUES('UuABC');
check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
INSERT INTO t1 VALUES('uuABC');
check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
alter table t1 add b int;
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1);
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',2);
delete from t1 where b=1;
INSERT INTO t1 VALUES('UUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1);
check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3);
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',4);
delete from t1 where b=3;
INSERT INTO t1 VALUES('uUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3);
check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
drop table t1;
set names utf8;
create table t1 (s1 char(5) character set utf8);
insert into t1 values
('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
create index it1 on t1 (s1);
select s1 as before_delete_general_ci from t1 where s1 like 'ペテ%';
before_delete_general_ci
ペテルグル
delete from t1 where s1 = 'Y';
select s1 as after_delete_general_ci from t1 where s1 like 'ペテ%';
after_delete_general_ci
ペテルグル
drop table t1;
set names utf8;
create table t1 (s1 char(5) character set utf8 collate utf8_unicode_ci);
insert into t1 values
('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
create index it1 on t1 (s1);
select s1 as before_delete_unicode_ci from t1 where s1 like 'ペテ%';
before_delete_unicode_ci
ペテルグル
delete from t1 where s1 = 'Y';
select s1 as after_delete_unicode_ci from t1 where s1 like 'ペテ%';
after_delete_unicode_ci
ペテルグル
drop table t1;
set names utf8;
create table t1 (s1 char(5) character set utf8 collate utf8_bin);
insert into t1 values
('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
create index it1 on t1 (s1);
select s1 as before_delete_bin from t1 where s1 like 'ペテ%';
before_delete_bin
ペテルグル
delete from t1 where s1 = 'Y';
select s1 as after_delete_bin from t1 where s1 like 'ペテ%';
after_delete_bin
ペテルグル
drop table t1;
set names utf8;
create table t1 (a varchar(30) not null primary key)
engine=innodb default character set utf8 collate utf8_general_ci;
insert into t1 values ('あいうえおかきくけこさしすせそ');
insert into t1 values ('さしすせそかきくけこあいうえお');
select a as gci1 from t1 where a like 'さしすせそかきくけこあいうえお%';
gci1
さしすせそかきくけこあいうえお
select a as gci2 from t1 where a like 'あいうえおかきくけこさしすせそ';
gci2
あいうえおかきくけこさしすせそ
drop table t1;
set names utf8;
create table t1 (a varchar(30) not null primary key)
engine=innodb default character set utf8 collate utf8_unicode_ci;
insert into t1 values ('あいうえおかきくけこさしすせそ');
insert into t1 values ('さしすせそかきくけこあいうえお');
select a as uci1 from t1 where a like 'さしすせそかきくけこあいうえお%';
uci1
さしすせそかきくけこあいうえお
select a as uci2 from t1 where a like 'あいうえおかきくけこさしすせそ';
uci2
あいうえおかきくけこさしすせそ
drop table t1;
set names utf8;
create table t1 (a varchar(30) not null primary key)
engine=innodb default character set utf8 collate utf8_bin;
insert into t1 values ('あいうえおかきくけこさしすせそ');
insert into t1 values ('さしすせそかきくけこあいうえお');
select a as bin1 from t1 where a like 'さしすせそかきくけこあいうえお%';
bin1
さしすせそかきくけこあいうえお
select a as bin2 from t1 where a like 'あいうえおかきくけこさしすせそ';
bin2
あいうえおかきくけこさしすせそ
drop table t1;
SET NAMES utf8;
CREATE TABLE t1 (id int PRIMARY KEY,
a varchar(16) collate utf8_unicode_ci NOT NULL default '',
b int,
f varchar(128) default 'XXX',
INDEX (a(4))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO t1(id, a, b) VALUES
(1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30),
(4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40),
(7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50),
(10, 'eeeee', 40), (11, 'bbbbbb', 60);
SELECT id, a, b FROM t1;
id a b
1 cccc 50
2 cccc 70
3 cccc 30
4 cccc 30
5 cccc 20
6 bbbbbb 40
7 dddd 30
8 aaaa 10
9 aaaa 50
10 eeeee 40
11 bbbbbb 60
SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';
id a b
8 aaaa 10
9 aaaa 50
6 bbbbbb 40
11 bbbbbb 60
SELECT id, a FROM t1 WHERE a='bbbbbb';
id a
6 bbbbbb
11 bbbbbb
SELECT id, a FROM t1 WHERE a='bbbbbb' ORDER BY b;
id a
6 bbbbbb
11 bbbbbb
DROP TABLE t1;
SET NAMES utf8;
CREATE TABLE t1 (
a CHAR(13) DEFAULT '',
INDEX(a)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 VALUES
('Käli Käli 2-4'), ('Käli Käli 2-4'),
('Käli Käli 2+4'), ('Käli Käli 2+4'),
('Käli Käli 2-6'), ('Käli Käli 2-6');
INSERT INTO t1 SELECT * FROM t1;
CREATE TABLE t2 (
a CHAR(13) DEFAULT '',
INDEX(a)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO t2 VALUES
('Kali Kali 2-4'), ('Kali Kali 2-4'),
('Kali Kali 2+4'), ('Kali Kali 2+4'),
('Kali Kali 2-6'), ('Kali Kali 2-6');
INSERT INTO t2 SELECT * FROM t2;
SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4';
a
Käli Käli 2+4
Käli Käli 2+4
Käli Käli 2+4
Käli Käli 2+4
SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4';
a
Kali Kali 2+4
Kali Kali 2+4
Kali Kali 2+4
Kali Kali 2+4
EXPLAIN SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 40 NULL 4 Using where; Using index
EXPLAIN SELECT a FROM t1 WHERE a = 'Käli Käli 2+4';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a a 40 const 4 Using where; Using index
EXPLAIN SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range a a 14 NULL 4 Using where; Using index
EXPLAIN SELECT a FROM t2 WHERE a = 'Kali Kali 2+4';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 14 const 4 Using where; Using index
DROP TABLE t1,t2;
CREATE TABLE t1 (
a char(255) DEFAULT '',
KEY(a(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 VALUES ('Käli Käli 2-4');
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
a
Käli Käli 2-4
INSERT INTO t1 VALUES ('Käli Käli 2-4');
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
a
Käli Käli 2-4
Käli Käli 2-4
DROP TABLE t1;
CREATE TABLE t1 (
a char(255) DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 VALUES ('Käli Käli 2-4');
INSERT INTO t1 VALUES ('Käli Käli 2-4');
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
a
Käli Käli 2-4
Käli Käli 2-4
ALTER TABLE t1 ADD KEY (a(10));
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
a
Käli Käli 2-4
Käli Käli 2-4
DROP TABLE t1;
SET NAMES latin2;
CREATE TABLE t1 (
id int(11) NOT NULL default '0',
tid int(11) NOT NULL default '0',
val text NOT NULL,
INDEX idx(tid, val(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES
(40988,72,'VOLN� ADSL'),(41009,72,'VOLN� ADSL'),
(41032,72,'VOLN� ADSL'),(41038,72,'VOLN� ADSL'),
(41063,72,'VOLN� ADSL'),(41537,72,'VOLN� ADSL Office'),
(42141,72,'VOLN� ADSL'),(42565,72,'VOLN� ADSL Combi'),
(42749,72,'VOLN� ADSL'),(44205,72,'VOLN� ADSL');
SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNY ADSL';
id tid val
40988 72 VOLN� ADSL
41009 72 VOLN� ADSL
41032 72 VOLN� ADSL
41038 72 VOLN� ADSL
41063 72 VOLN� ADSL
42141 72 VOLN� ADSL
42749 72 VOLN� ADSL
44205 72 VOLN� ADSL
SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL';
id tid val
40988 72 VOLN� ADSL
41009 72 VOLN� ADSL
41032 72 VOLN� ADSL
41038 72 VOLN� ADSL
41063 72 VOLN� ADSL
42141 72 VOLN� ADSL
42749 72 VOLN� ADSL
44205 72 VOLN� ADSL
SELECT * FROM t1 WHERE tid=72 and val LIKE '%VOLN� ADSL';
id tid val
40988 72 VOLN� ADSL
41009 72 VOLN� ADSL
41032 72 VOLN� ADSL
41038 72 VOLN� ADSL
41063 72 VOLN� ADSL
42141 72 VOLN� ADSL
42749 72 VOLN� ADSL
44205 72 VOLN� ADSL
ALTER TABLE t1 DROP KEY idx;
ALTER TABLE t1 ADD KEY idx (tid,val(11));
SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL';
id tid val
40988 72 VOLN� ADSL
41009 72 VOLN� ADSL
41032 72 VOLN� ADSL
41038 72 VOLN� ADSL
41063 72 VOLN� ADSL
42141 72 VOLN� ADSL
42749 72 VOLN� ADSL
44205 72 VOLN� ADSL
DROP TABLE t1;
create table t1(a char(200) collate utf8_unicode_ci NOT NULL default '')
default charset=utf8 collate=utf8_unicode_ci;
insert into t1 values (unhex('65')), (unhex('C3A9')), (unhex('65'));
explain select distinct a from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary
select distinct a from t1;
a
e
explain select a from t1 group by a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort
select a from t1 group by a;
a
e
drop table t1;
create table t1(a char(10)) default charset utf8;
insert into t1 values ('123'), ('456');
explain
select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Y ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
1 SIMPLE Z ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
substr(Z.a,-1) a
3 123
6 456
drop table t1;
SET CHARACTER SET utf8;
SHOW VARIABLES LIKE 'character\_set\_%';
Variable_name Value
character_set_client utf8
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
CREATE DATABASE crashtest DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE crashtest;
CREATE TABLE crashtest (crash char(10)) DEFAULT CHARSET=utf8;
INSERT INTO crashtest VALUES ('35'), ('36'), ('37');
SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8);
crash
35
36
37
INSERT INTO crashtest VALUES ('-1000');
EXPLAIN SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE crashtest ALL NULL NULL NULL NULL 4 Using filesort
SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8);
crash
-1000
35
36
37
Warnings:
Warning 1300 Invalid utf8 character string: 'FFFFFC'
DROP TABLE crashtest;
DROP DATABASE crashtest;
USE test;
SET CHARACTER SET default;
CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8;
INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa');
SELECT id FROM t1;
id
xxx
aa
yyy
aa
SELECT DISTINCT id FROM t1;
id
xxx
aa
yyy
SELECT DISTINCT id FROM t1 ORDER BY id;
id
aa
xxx
yyy
DROP TABLE t1;
create table t1 (
a varchar(26) not null
) default character set utf8;
insert into t1 (a) values ('abcdefghijklmnopqrstuvwxyz');
select * from t1;
a
abcdefghijklmnopqrstuvwxyz
alter table t1 change a a varchar(20) character set utf8 not null;
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
select * from t1;
a
abcdefghijklmnopqrst
alter table t1 change a a char(15) character set utf8 not null;
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
select * from t1;
a
abcdefghijklmno
alter table t1 change a a char(10) character set utf8 not null;
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
select * from t1;
a
abcdefghij
alter table t1 change a a varchar(5) character set utf8 not null;
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
select * from t1;
a
abcde
drop table t1;
create table t1 (
a varchar(4000) not null
) default character set utf8;
insert into t1 values (repeat('a',4000));
alter table t1 change a a varchar(3000) character set utf8 not null;
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
select length(a) from t1;
length(a)
3000
drop table t1;
set names utf8;
select hex(char(1 using utf8));
hex(char(1 using utf8))
01
select char(0xd1,0x8f using utf8);
char(0xd1,0x8f using utf8)
я
select char(0xd18f using utf8);
char(0xd18f using utf8)
я
select char(53647 using utf8);
char(53647 using utf8)
я
select char(0xff,0x8f using utf8);
char(0xff,0x8f using utf8)
Warnings:
Warning 1300 Invalid utf8 character string: 'FF8F'
select convert(char(0xff,0x8f) using utf8);
convert(char(0xff,0x8f) using utf8)
Warnings:
Warning 1300 Invalid utf8 character string: 'FF8F'
set sql_mode=traditional;
select char(0xff,0x8f using utf8);
char(0xff,0x8f using utf8)
NULL
Warnings:
Warning 1300 Invalid utf8 character string: 'FF8F'
select char(195 using utf8);
char(195 using utf8)
NULL
Warnings:
Warning 1300 Invalid utf8 character string: 'C3'
select char(196 using utf8);
char(196 using utf8)
NULL
Warnings:
Warning 1300 Invalid utf8 character string: 'C4'
select char(2557 using utf8);
char(2557 using utf8)
NULL
Warnings:
Warning 1300 Invalid utf8 character string: 'FD'
select convert(char(0xff,0x8f) using utf8);
convert(char(0xff,0x8f) using utf8)
NULL
Warnings:
Warning 1300 Invalid utf8 character string: 'FF8F'
select hex(convert(char(2557 using latin1) using utf8));
hex(convert(char(2557 using latin1) using utf8))
09C3BD
select hex(char(195));
hex(char(195))
C3
select hex(char(196));
hex(char(196))
C4
select hex(char(2557));
hex(char(2557))
09FD
set names utf8;
create table t1 (a char(1)) default character set utf8;
create table t2 (a char(1)) default character set utf8;
insert into t1 values('a'),('a'),(0xE38182),(0xE38182);
insert into t1 values('i'),('i'),(0xE38184),(0xE38184);
select * from t1 union distinct select * from t2;
a
a
あ
i
い
drop table t1,t2;
set names utf8;
create table t1 (a char(10), b varchar(10));
insert into t1 values ('bar','kostja');
insert into t1 values ('kostja','bar');
prepare my_stmt from "select * from t1 where a=?";
set @a:='bar';
execute my_stmt using @a;
a b
bar kostja
set @a:='kostja';
execute my_stmt using @a;
a b
kostja bar
set @a:=null;
execute my_stmt using @a;
a b
drop table if exists t1;
drop table if exists t1;
drop view if exists v1, v2;
set names utf8;
create table t1(col1 varchar(12) character set utf8 collate utf8_unicode_ci);
insert into t1 values('t1_val');
create view v1 as select 'v1_val' as col1;
select coercibility(col1), collation(col1) from v1;
coercibility(col1) collation(col1)
4 utf8_general_ci
create view v2 as select col1 from v1 union select col1 from t1;
select coercibility(col1), collation(col1)from v2;
coercibility(col1) collation(col1)
2 utf8_unicode_ci
2 utf8_unicode_ci
drop view v1, v2;
create view v1 as select 'v1_val' collate utf8_swedish_ci as col1;
select coercibility(col1), collation(col1) from v1;
coercibility(col1) collation(col1)
0 utf8_swedish_ci
create view v2 as select col1 from v1 union select col1 from t1;
select coercibility(col1), collation(col1) from v2;
coercibility(col1) collation(col1)
0 utf8_swedish_ci
0 utf8_swedish_ci
drop view v1, v2;
drop table t1;
set names utf8;
create table t1 (a varchar(10) character set latin1, b int);
insert into t1 values ('a',1);
select concat(a, if(b>10, N'x', N'y')) from t1;
concat(a, if(b>10, N'x', N'y'))
ay
select concat(a, if(b>10, N'æ', N'ß')) from t1;
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'
drop table t1;
set names utf8;
create table t1 (a varchar(10) character set latin1, b int);
insert into t1 values ('a',1);
select concat(a, if(b>10, _utf8'x', _utf8'y')) from t1;
concat(a, if(b>10, _utf8'x', _utf8'y'))
ay
select concat(a, if(b>10, _utf8'æ', _utf8'ß')) from t1;
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'
drop table t1;
set names utf8;
create table t1 (a varchar(10) character set latin1, b int);
insert into t1 values ('a',1);
select concat(a, if(b>10, _utf8 0x78, _utf8 0x79)) from t1;
concat(a, if(b>10, _utf8 0x78, _utf8 0x79))
ay
select concat(a, if(b>10, _utf8 0xC3A6, _utf8 0xC3AF)) from t1;
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'
drop table t1;
set names utf8;
create table t1 (a varchar(10) character set latin1, b int);
insert into t1 values ('a',1);
select concat(a, if(b>10, 'x' 'x', 'y' 'y')) from t1;
concat(a, if(b>10, 'x' 'x', 'y' 'y'))
ayy
select concat(a, if(b>10, 'x' 'æ', 'y' 'ß')) from t1;
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'
drop table t1;
CREATE TABLE t1 (
colA int(11) NOT NULL,
colB varchar(255) character set utf8 NOT NULL,
PRIMARY KEY (colA)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 (colA, colB) VALUES (1, 'foo'), (2, 'foo bar');
CREATE TABLE t2 (
colA int(11) NOT NULL,
colB varchar(255) character set utf8 NOT NULL,
KEY bad (colA,colB(3))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t2 (colA, colB) VALUES (1, 'foo'),(2, 'foo bar');
SELECT * FROM t1 JOIN t2 ON t1.colA=t2.colA AND t1.colB=t2.colB
WHERE t1.colA < 3;
colA colB colA colB
1 foo 1 foo
2 foo bar 2 foo bar
DROP TABLE t1, t2;
SELECT 'н1234567890' UNION SELECT _binary '1';
н1234567890
н1234567890
1
SELECT 'н1234567890' UNION SELECT 1;
н1234567890
н1234567890
1
SELECT '1' UNION SELECT 'н1234567890';
1
1
н1234567890
SELECT 1 UNION SELECT 'н1234567890';
1
1
н1234567890
CREATE TABLE t1 (c VARCHAR(11)) CHARACTER SET utf8;
CREATE TABLE t2 (b CHAR(1) CHARACTER SET binary, i INT);
INSERT INTO t1 (c) VALUES ('н1234567890');
INSERT INTO t2 (b, i) VALUES ('1', 1);
SELECT c FROM t1 UNION SELECT b FROM t2;
c
н1234567890
1
SELECT c FROM t1 UNION SELECT i FROM t2;
c
н1234567890
1
SELECT b FROM t2 UNION SELECT c FROM t1;
b
1
н1234567890
SELECT i FROM t2 UNION SELECT c FROM t1;
i
1
н1234567890
DROP TABLE t1, t2;
set sql_mode=traditional;
select hex(char(0xFF using utf8));
hex(char(0xFF using utf8))
NULL
Warnings:
Warning 1300 Invalid utf8 character string: 'FF'
select hex(convert(0xFF using utf8));
hex(convert(0xFF using utf8))
NULL
Warnings:
Warning 1300 Invalid utf8 character string: 'FF'
select hex(_utf8 0x616263FF);
ERROR HY000: Invalid utf8 character string: 'FF'
select hex(_utf8 X'616263FF');
ERROR HY000: Invalid utf8 character string: 'FF'
select hex(_utf8 B'001111111111');
ERROR HY000: Invalid utf8 character string: 'FF'
select (_utf8 X'616263FF');
ERROR HY000: Invalid utf8 character string: 'FF'
set sql_mode=default;
select hex(char(0xFF using utf8));
hex(char(0xFF using utf8))
Warnings:
Warning 1300 Invalid utf8 character string: 'FF'
select hex(convert(0xFF using utf8));
hex(convert(0xFF using utf8))
Warnings:
Warning 1300 Invalid utf8 character string: 'FF'
select hex(_utf8 0x616263FF);
ERROR HY000: Invalid utf8 character string: 'FF'
select hex(_utf8 X'616263FF');
ERROR HY000: Invalid utf8 character string: 'FF'
select hex(_utf8 B'001111111111');
ERROR HY000: Invalid utf8 character string: 'FF'
select (_utf8 X'616263FF');
ERROR HY000: Invalid utf8 character string: 'FF'
#
# Bug#44131 Binary-mode "order by" returns records in incorrect order for UTF-8 strings
#
CREATE TABLE t1 (id int not null primary key, name varchar(10)) character set utf8;
INSERT INTO t1 VALUES
(2,'一二三01'),(3,'一二三09'),(4,'一二三02'),(5,'一二三08'),
(6,'一二三11'),(7,'一二三91'),(8,'一二三21'),(9,'一二三81');
SELECT * FROM t1 ORDER BY BINARY(name);
id name
2 一二三01
4 一二三02
5 一二三08
3 一二三09
6 一二三11
8 一二三21
9 一二三81
7 一二三91
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
INSERT INTO t1 VALUES (70000, 1092), (70001, 1085), (70002, 1065);
SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;
CONVERT(a, CHAR) CONVERT(b, CHAR)
70002 1065
70001 1085
70000 1092
SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1;
CONVERT(a, CHAR) CONVERT(b, CHAR)
70000 1092
70001 1085
70002 1065
ALTER TABLE t1 ADD UNIQUE (b);
SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;
CONVERT(a, CHAR) CONVERT(b, CHAR)
70002 1065
70001 1085
70000 1092
DROP INDEX b ON t1;
SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;
CONVERT(a, CHAR) CONVERT(b, CHAR)
70002 1065
70001 1085
70000 1092
ALTER TABLE t1 ADD INDEX (b);
SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) from t1 GROUP BY b;
CONVERT(a, CHAR) CONVERT(b, CHAR)
70002 1065
70001 1085
70000 1092
DROP TABLE t1;
End of 5.0 tests
SELECT LENGTH(RPAD(0.0115E88, 61297, _utf8'яэюя'));
LENGTH(RPAD(0.0115E88, 61297, _utf8'яэюя'))
122587
SELECT LENGTH(RPAD(0.0115E88, 61297, _utf8'йцуя'));
LENGTH(RPAD(0.0115E88, 61297, _utf8'йцуя'))
122587
SELECT HEX(RPAD(0x20, 2, _utf8 0xD18F));
HEX(RPAD(0x20, 2, _utf8 0xD18F))
20D1
SELECT HEX(RPAD(0x20, 4, _utf8 0xD18F));
HEX(RPAD(0x20, 4, _utf8 0xD18F))
20D18FD1
SELECT HEX(LPAD(0x20, 2, _utf8 0xD18F));
HEX(LPAD(0x20, 2, _utf8 0xD18F))
D120
SELECT HEX(LPAD(0x20, 4, _utf8 0xD18F));
HEX(LPAD(0x20, 4, _utf8 0xD18F))
D18FD120
SELECT HEX(RPAD(_utf8 0xD18F, 3, 0x20));
HEX(RPAD(_utf8 0xD18F, 3, 0x20))
D18F20
SELECT HEX(LPAD(_utf8 0xD18F, 3, 0x20));
HEX(LPAD(_utf8 0xD18F, 3, 0x20))
20D18F
SELECT HEX(INSERT(_utf8 0xD18F, 2, 1, 0x20));
HEX(INSERT(_utf8 0xD18F, 2, 1, 0x20))
D120
SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20));
HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20))
D120D18E
#
# Bug#11752408 - 43593: DUMP/BACKUP/RESTORE/UPGRADE TOOLS FAILS BECAUSE OF UTF8_GENERAL_CI
#
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci);
INSERT INTO t1 VALUES ('a'),('r'),('s'),(_latin1 0xDF),(_latin1 0xF7),('t'),('z');
SELECT * FROM t1 ORDER BY a;
a
a
r
s
t
z
ß
÷
SELECT a, COUNT(*) FROM t1 GROUP BY a;
a COUNT(*)
a 1
r 1
s 1
t 1
z 1
ß 1
÷ 1
DROP TABLE t1;
End of 5.1 tests
Start of 5.4 tests
SET NAMES utf8mb3;
SHOW VARIABLES LIKE 'character_set_results%';
Variable_name Value
character_set_results utf8
CREATE TABLE t1 (a CHAR CHARACTER SET utf8mb3 COLLATE utf8mb3_bin);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` char(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
SELECT _utf8mb3'test';
test
test
CREATE TABLE t1 (
clipid INT NOT NULL,
Tape TINYTEXT,
PRIMARY KEY (clipid),
KEY tape(Tape(255))
) CHARACTER SET=utf8;
ALTER TABLE t1 ADD mos TINYINT DEFAULT 0 AFTER clipid;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`clipid` int(11) NOT NULL,
`mos` tinyint(4) DEFAULT '0',
`Tape` tinytext,
PRIMARY KEY (`clipid`),
KEY `tape` (`Tape`(255))
) ENGINE=MyISAM DEFAULT CHARSET=utf8
DROP TABLE t1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
predicted_order int NOT NULL,
utf8_encoding VARCHAR(10) NOT NULL
) CHARACTER SET utf8;
INSERT INTO t1 VALUES (19, x'E0B696'), (30, x'E0B69AE0B798'), (61, x'E0B6AF'), (93, x'E0B799'), (52, x'E0B6A6'), (73, x'E0B6BBE0B78AE2808D'), (3, x'E0B686'), (56, x'E0B6AA'), (55, x'E0B6A9'), (70, x'E0B6B9'), (94, x'E0B79A'), (80, x'E0B785'), (25, x'E0B69AE0B791'), (48, x'E0B6A2'), (13, x'E0B690'), (86, x'E0B793'), (91, x'E0B79F'), (81, x'E0B786'), (79, x'E0B784'), (14, x'E0B691'), (99, x'E0B78A'), (8, x'E0B68B'), (68, x'E0B6B7'), (22, x'E0B69A'), (16, x'E0B693'), (33, x'E0B69AE0B7B3'), (38, x'E0B69AE0B79D'), (21, x'E0B683'), (11, x'E0B68E'), (77, x'E0B782'), (40, x'E0B69AE0B78A'), (101, x'E0B78AE2808DE0B6BB'), (35, x'E0B69AE0B79A'), (1, x'E0B7B4'), (9, x'E0B68C'), (96, x'E0B79C'), (6, x'E0B689'), (95, x'E0B79B'), (88, x'E0B796'), (64, x'E0B6B3'), (26, x'E0B69AE0B792'), (82, x'E0B78F'), (28, x'E0B69AE0B794'), (39, x'E0B69AE0B79E'), (97, x'E0B79D'), (2, x'E0B685'), (75, x'E0B780'), (34, x'E0B69AE0B799'), (69, x'E0B6B8'), (83, x'E0B790'), (18, x'E0B695'), (90, x'E0B7B2'), (17, x'E0B694'), (72, x'E0B6BB'), (66, x'E0B6B5'), (59, x'E0B6AD'), (44, x'E0B69E'), (15, x'E0B692'), (23, x'E0B69AE0B78F'), (65, x'E0B6B4'), (42, x'E0B69C'), (63, x'E0B6B1'), (85, x'E0B792'), (47, x'E0B6A1'), (49, x'E0B6A3'), (92, x'E0B7B3'), (78, x'E0B783'), (36, x'E0B69AE0B79B'), (4, x'E0B687'), (24, x'E0B69AE0B790'), (87, x'E0B794'), (37, x'E0B69AE0B79C'), (32, x'E0B69AE0B79F'), (29, x'E0B69AE0B796'), (43, x'E0B69D'), (62, x'E0B6B0'), (100, x'E0B78AE2808DE0B6BA'), (60, x'E0B6AE'), (45, x'E0B69F'), (12, x'E0B68F'), (46, x'E0B6A0'), (50, x'E0B6A5'), (51, x'E0B6A4'), (5, x'E0B688'), (76, x'E0B781'), (89, x'E0B798'), (74, x'E0B6BD'), (10, x'E0B68D'), (57, x'E0B6AB'), (71, x'E0B6BA'), (58, x'E0B6AC'), (27, x'E0B69AE0B793'), (54, x'E0B6A8'), (84, x'E0B791'), (31, x'E0B69AE0B7B2'), (98, x'E0B79E'), (53, x'E0B6A7'), (41, x'E0B69B'), (67, x'E0B6B6'), (7, x'E0B68A'), (20, x'E0B682');
SELECT predicted_order, hex(utf8_encoding) FROM t1 ORDER BY utf8_encoding COLLATE utf8_sinhala_ci;
predicted_order hex(utf8_encoding)
1 E0B7B4
2 E0B685
3 E0B686
4 E0B687
5 E0B688
6 E0B689
7 E0B68A
8 E0B68B
9 E0B68C
10 E0B68D
11 E0B68E
12 E0B68F
13 E0B690
14 E0B691
15 E0B692
16 E0B693
17 E0B694
18 E0B695
19 E0B696
20 E0B682
21 E0B683
22 E0B69A
23 E0B69AE0B78F
24 E0B69AE0B790
25 E0B69AE0B791
26 E0B69AE0B792
27 E0B69AE0B793
28 E0B69AE0B794
29 E0B69AE0B796
30 E0B69AE0B798
31 E0B69AE0B7B2
32 E0B69AE0B79F
33 E0B69AE0B7B3
34 E0B69AE0B799
35 E0B69AE0B79A
36 E0B69AE0B79B
37 E0B69AE0B79C
38 E0B69AE0B79D
39 E0B69AE0B79E
40 E0B69AE0B78A
41 E0B69B
42 E0B69C
43 E0B69D
44 E0B69E
45 E0B69F
46 E0B6A0
47 E0B6A1
48 E0B6A2
49 E0B6A3
50 E0B6A5
51 E0B6A4
52 E0B6A6
53 E0B6A7
54 E0B6A8
55 E0B6A9
56 E0B6AA
57 E0B6AB
58 E0B6AC
59 E0B6AD
60 E0B6AE
61 E0B6AF
62 E0B6B0
63 E0B6B1
64 E0B6B3
65 E0B6B4
66 E0B6B5
67 E0B6B6
68 E0B6B7
69 E0B6B8
70 E0B6B9
71 E0B6BA
72 E0B6BB
73 E0B6BBE0B78AE2808D
74 E0B6BD
75 E0B780
76 E0B781
77 E0B782
78 E0B783
79 E0B784
80 E0B785
81 E0B786
82 E0B78F
83 E0B790
84 E0B791
85 E0B792
86 E0B793
87 E0B794
88 E0B796
89 E0B798
90 E0B7B2
91 E0B79F
92 E0B7B3
93 E0B799
94 E0B79A
95 E0B79B
96 E0B79C
97 E0B79D
98 E0B79E
99 E0B78A
100 E0B78AE2808DE0B6BA
101 E0B78AE2808DE0B6BB
DROP TABLE t1;
SET NAMES utf8 COLLATE utf8_sinhala_ci;
CREATE TABLE t1 (s1 VARCHAR(10) COLLATE utf8_sinhala_ci);
INSERT INTO t1 VALUES ('a'),('ae'),('af');
SELECT s1,hex(s1) FROM t1 ORDER BY s1;
s1 hex(s1)
a 61
ae 6165
af 6166
SELECT * FROM t1 ORDER BY s1;
s1
a
ae
af
DROP TABLE t1;
End of 5.4 tests
#
# Start of 5.5 tests
#
#
# Bug#52520 Difference in tinytext utf column metadata
#
CREATE TABLE t1 (
s1 TINYTEXT CHARACTER SET utf8,
s2 TEXT CHARACTER SET utf8,
s3 MEDIUMTEXT CHARACTER SET utf8,
s4 LONGTEXT CHARACTER SET utf8
);
SET NAMES utf8, @@character_set_results=NULL;
SELECT *, HEX(s1) FROM t1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def test t1 t1 s1 s1 252 255 0 Y 16 0 33
def test t1 t1 s2 s2 252 65535 0 Y 16 0 33
def test t1 t1 s3 s3 252 16777215 0 Y 16 0 33
def test t1 t1 s4 s4 252 4294967295 0 Y 16 0 33
def HEX(s1) 253 4590 0 Y 0 0 33
s1 s2 s3 s4 HEX(s1)
SET NAMES latin1;
SELECT *, HEX(s1) FROM t1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def test t1 t1 s1 s1 252 255 0 Y 16 0 8
def test t1 t1 s2 s2 252 65535 0 Y 16 0 8
def test t1 t1 s3 s3 252 16777215 0 Y 16 0 8
def test t1 t1 s4 s4 252 4294967295 0 Y 16 0 8
def HEX(s1) 253 1530 0 Y 0 0 8
s1 s2 s3 s4 HEX(s1)
SET NAMES utf8;
SELECT *, HEX(s1) FROM t1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def test t1 t1 s1 s1 252 765 0 Y 16 0 33
def test t1 t1 s2 s2 252 196605 0 Y 16 0 33
def test t1 t1 s3 s3 252 50331645 0 Y 16 0 33
def test t1 t1 s4 s4 252 4294967295 0 Y 16 0 33
def HEX(s1) 253 4590 0 Y 0 0 33
s1 s2 s3 s4 HEX(s1)
CREATE TABLE t2 AS SELECT CONCAT(s1) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`CONCAT(s1)` varchar(255) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1, t2;
SET NAMES utf8;
#
# Start of WL#2649 Number-to-string conversions
#
select hex(concat(1));
hex(concat(1))
31
create table t1 as select concat(1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select hex(c1) from t1;
hex(c1)
31
drop table t1;
select hex(concat(18446744073709551615));
hex(concat(18446744073709551615))
3138343436373434303733373039353531363135
create table t1 as select concat(18446744073709551615) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select hex(c1) from t1;
hex(c1)
3138343436373434303733373039353531363135
drop table t1;
select hex(concat(1.1));
hex(concat(1.1))
312E31
create table t1 as select concat(1.1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(4) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select hex(c1) from t1;
hex(c1)
312E31
drop table t1;
select hex(concat('a', 1+2)), charset(concat(1+2));
hex(concat('a', 1+2)) charset(concat(1+2))
6133 utf8
create table t1 as select concat(1+2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1-2));
hex(concat(1-2))
2D31
create table t1 as select concat(1-2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1*2));
hex(concat(1*2))
32
create table t1 as select concat(1*2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1/2));
hex(concat(1/2))
302E35303030
create table t1 as select concat(1/2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(7) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1 div 2));
hex(concat(1 div 2))
30
create table t1 as select concat(1 div 2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1 % 2));
hex(concat(1 % 2))
31
create table t1 as select concat(1 % 2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(-1));
hex(concat(-1))
2D31
create table t1 as select concat(-1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(-(1+2)));
hex(concat(-(1+2)))
2D33
create table t1 as select concat(-(1+2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(4) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1|2));
hex(concat(1|2))
33
create table t1 as select concat(1|2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(1&2));
hex(concat(1&2))
30
create table t1 as select concat(1&2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(bit_count(12)));
hex(concat(bit_count(12)))
32
create table t1 as select concat(bit_count(12)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(2<<1));
hex(concat(2<<1))
34
create table t1 as select concat(2<<1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(2>>1));
hex(concat(2>>1))
31
create table t1 as select concat(2>>1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(~0));
hex(concat(~0))
3138343436373434303733373039353531363135
create table t1 as select concat(~0) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(3^2));
hex(concat(3^2))
31
create table t1 as select concat(3^2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(abs(-2)));
hex(concat(abs(-2)))
32
create table t1 as select concat(abs(-2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(exp(2)),1));
hex(left(concat(exp(2)),1))
37
create table t1 as select concat(exp(2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(log(2)),1));
hex(left(concat(log(2)),1))
30
create table t1 as select concat(log(2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(log2(2)),1));
hex(left(concat(log2(2)),1))
31
create table t1 as select concat(log2(2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(log10(2)),1));
hex(left(concat(log10(2)),1))
30
create table t1 as select concat(log10(2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(sqrt(2)),1));
hex(left(concat(sqrt(2)),1))
31
create table t1 as select concat(sqrt(2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(pow(2,2)),1));
hex(left(concat(pow(2,2)),1))
34
create table t1 as select concat(pow(2,2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(acos(0.5)),1));
hex(left(concat(acos(0.5)),1))
31
create table t1 as select concat(acos(0.5)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(asin(0.5)),1));
hex(left(concat(asin(0.5)),1))
30
create table t1 as select concat(asin(0.5)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(atan(0.5)),1));
hex(left(concat(atan(0.5)),1))
30
create table t1 as select concat(atan(0.5)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(cos(0.5)),1));
hex(left(concat(cos(0.5)),1))
30
create table t1 as select concat(cos(0.5)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(sin(0.5)),1));
hex(left(concat(sin(0.5)),1))
30
create table t1 as select concat(sin(0.5)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(tan(0.5)),1));
hex(left(concat(tan(0.5)),1))
30
create table t1 as select concat(tan(0.5)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(degrees(0)));
hex(concat(degrees(0)))
30
create table t1 as select concat(degrees(0)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(radians(0)));
hex(concat(radians(0)))
30
create table t1 as select concat(radians(0)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ceiling(0.5)));
hex(concat(ceiling(0.5)))
31
create table t1 as select concat(ceiling(0.5)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(4) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(floor(0.5)));
hex(concat(floor(0.5)))
30
create table t1 as select concat(floor(0.5)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(4) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(round(0.5)));
hex(concat(round(0.5)))
31
create table t1 as select concat(round(0.5)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(sign(0.5)));
hex(concat(sign(0.5)))
31
create table t1 as select concat(sign(0.5)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(rand()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(length('a')));
hex(concat(length('a')))
31
create table t1 as select concat(length('a')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(char_length('a')));
hex(concat(char_length('a')))
31
create table t1 as select concat(char_length('a')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(bit_length('a')));
hex(concat(bit_length('a')))
38
create table t1 as select concat(bit_length('a')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(coercibility('a')));
hex(concat(coercibility('a')))
34
create table t1 as select concat(coercibility('a')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(locate('a','a')));
hex(concat(locate('a','a')))
31
create table t1 as select concat(locate('a','a')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(11) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(field('c','a','b','c')));
hex(concat(field('c','a','b','c')))
33
create table t1 as select concat(field('c','a','b','c')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ascii(61)));
hex(concat(ascii(61)))
3534
create table t1 as select concat(ascii(61)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ord(61)));
hex(concat(ord(61)))
3534
create table t1 as select concat(ord(61)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(find_in_set('b','a,b,c,d')));
hex(concat(find_in_set('b','a,b,c,d')))
32
create table t1 as select concat(find_in_set('b','a,b,c,d')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select md5('a'), hex(md5('a'));
md5('a') hex(md5('a'))
0cc175b9c0f1b6a831c399e269772661 3063633137356239633066316236613833316333393965323639373732363631
create table t1 as select md5('a') as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select old_password('a'), hex(old_password('a'));
old_password('a') hex(old_password('a'))
60671c896665c3fa 36303637316338393636363563336661
Warnings:
Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead
Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead
create table t1 as select old_password('a') as c1;
Warnings:
Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select password('a'), hex(password('a'));
password('a') hex(password('a'))
*667F407DE7C6AD07358FA38DAED7828A72014B4E 2A36363746343037444537433641443037333538464133384441454437383238413732303134423445
create table t1 as select password('a') as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(41) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select sha('a'), hex(sha('a'));
sha('a') hex(sha('a'))
86f7e437faa5a7fce15d1ddcb9eaeaea377667b8 38366637653433376661613561376663653135643164646362396561656165613337373636376238
create table t1 as select sha('a') as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(40) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select sha1('a'), hex(sha1('a'));
sha1('a') hex(sha1('a'))
86f7e437faa5a7fce15d1ddcb9eaeaea377667b8 38366637653433376661613561376663653135643164646362396561656165613337373636376238
create table t1 as select sha1('a') as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(40) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast('-1' as signed)));
hex(concat(cast('-1' as signed)))
2D31
create table t1 as select concat(cast('-1' as signed)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast('1' as unsigned)));
hex(concat(cast('1' as unsigned)))
31
create table t1 as select concat(cast('1' as unsigned)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast(1/2 as decimal(5,5))));
hex(concat(cast(1/2 as decimal(5,5))))
302E3530303030
create table t1 as select concat(cast(1/2 as decimal(5,5))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(7) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(cast('2001-01-02 03:04:05' as date)));
hex(concat(cast('2001-01-02 03:04:05' as date)))
323030312D30312D3032
create table t1 as select concat(cast('2001-01-02 03:04:05' as date)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
c1
2001-01-02
drop table t1;
select hex(concat(cast('2001-01-02 03:04:05' as time)));
hex(concat(cast('2001-01-02 03:04:05' as time)))
30333A30343A3035
create table t1 as select concat(cast('2001-01-02 03:04:05' as time)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
c1
03:04:05
drop table t1;
select hex(concat(cast('2001-01-02' as datetime)));
hex(concat(cast('2001-01-02' as datetime)))
323030312D30312D30322030303A30303A3030
create table t1 as select concat(cast('2001-01-02' as datetime)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(19) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
c1
2001-01-02 00:00:00
drop table t1;
select hex(concat(least(1,2)));
hex(concat(least(1,2)))
31
create table t1 as select concat(least(1,2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(greatest(1,2)));
hex(concat(greatest(1,2)))
32
create table t1 as select concat(greatest(1,2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(case when 11 then 22 else 33 end));
hex(concat(case when 11 then 22 else 33 end))
3232
create table t1 as select concat(case when 11 then 22 else 33 end) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(coalesce(1,2)));
hex(concat(coalesce(1,2)))
31
create table t1 as select concat(coalesce(1,2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat_ws(1,2,3));
hex(concat_ws(1,2,3))
323133
create table t1 as select concat_ws(1,2,3) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(group_concat(1,2,3));
hex(group_concat(1,2,3))
313233
create table t1 as select group_concat(1,2,3) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` text CHARACTER SET utf8
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select 1 as c1 union select 'a';
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select hex(c1) from t1 order by c1;
hex(c1)
31
61
drop table t1;
create table t1 as select concat(last_insert_id()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(benchmark(0,0)));
hex(concat(benchmark(0,0)))
30
create table t1 as select concat(benchmark(0,0)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(sleep(0)));
hex(concat(sleep(0)))
30
create table t1 as select concat(sleep(0)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(is_free_lock('xxxx')));
hex(concat(is_free_lock('xxxx')))
31
create table t1 as select concat(is_free_lock('xxxx')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(is_used_lock('a')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(release_lock('a')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(crc32('')));
hex(concat(crc32('')))
30
create table t1 as select concat(crc32('')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(uncompressed_length('')));
hex(concat(uncompressed_length('')))
30
create table t1 as select concat(uncompressed_length('')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(connection_id()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(inet_aton('127.1.1.1')));
hex(concat(inet_aton('127.1.1.1')))
32313330373732323235
create table t1 as select concat(inet_aton('127.1.1.1')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(inet_ntoa(2130772225)));
hex(concat(inet_ntoa(2130772225)))
3132372E312E312E31
create table t1 as select concat(inet_ntoa(2130772225)) as c1;
select * from t1;
c1
127.1.1.1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(31) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select 1;
1
1
select hex(concat(row_count()));
hex(concat(row_count()))
2D31
create table t1 as select concat(row_count()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(found_rows()));
hex(concat(found_rows()))
30
create table t1 as select concat(found_rows()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(uuid_short()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(uuid()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(36) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select coercibility(uuid()), coercibility(cast('a' as char character set latin1));
coercibility(uuid()) coercibility(cast('a' as char character set latin1))
4 2
select charset(concat(uuid(), cast('a' as char character set latin1)));
charset(concat(uuid(), cast('a' as char character set latin1)))
latin1
create table t1 as select concat(uuid(), cast('a' as char character set latin1)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(37) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(master_pos_wait('non-existent',0,2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@a1:=1));
hex(concat(@a1:=1))
31
create table t1 as select concat(@a2:=2) as c1, @a3:=3 as c2;
select hex(c1) from t1;
hex(c1)
32
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT '',
`c2` int(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @a2=1;
select hex(concat(@a2));
hex(concat(@a2))
31
create table t1 as select concat(@a2) as c1, @a2 as c2;
select hex(c1) from t1;
hex(c1)
31
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`c2` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@a1:=sqrt(1)));
hex(concat(@a1:=sqrt(1)))
31
create table t1 as select concat(@a2:=sqrt(1)) as c1, @a3:=sqrt(1) as c2;
select hex(c1) from t1;
hex(c1)
31
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL,
`c2` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @a2=sqrt(1);
select hex(concat(@a2));
hex(concat(@a2))
31
create table t1 as select concat(@a2) as c1, @a2 as c2;
select hex(c1) from t1;
hex(c1)
31
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL,
`c2` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@a1:=1.1));
hex(concat(@a1:=1.1))
312E31
create table t1 as select concat(@a2:=1.1) as c1, @a3:=1.1 as c2;
select hex(c1) from t1;
hex(c1)
312E31
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(4) CHARACTER SET utf8 NOT NULL DEFAULT '',
`c2` decimal(2,1) NOT NULL DEFAULT '0.0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @a2=1.1;
select hex(concat(@a2));
hex(concat(@a2))
312E31
create table t1 as select concat(@a2) as c1, @a2 as c2;
select hex(c1) from t1;
hex(c1)
312E31
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(83) CHARACTER SET utf8 DEFAULT NULL,
`c2` decimal(65,30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(@@ft_max_word_len));
hex(concat(@@ft_max_word_len))
3834
create table t1 as select concat(@@ft_max_word_len) as c1;
select hex(c1) from t1;
hex(c1)
3834
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a'='a' IS TRUE));
hex(concat('a'='a' IS TRUE))
31
create table t1 as select concat('a'='a' IS TRUE) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a'='a' IS NOT TRUE));
hex(concat('a'='a' IS NOT TRUE))
30
create table t1 as select concat('a'='a' IS NOT TRUE) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(NOT 'a'='a'));
hex(concat(NOT 'a'='a'))
30
create table t1 as select concat(NOT 'a'='a') as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' IS NULL));
hex(concat('a' IS NULL))
30
create table t1 as select concat('a' IS NULL) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' IS NOT NULL));
hex(concat('a' IS NOT NULL))
31
create table t1 as select concat('a' IS NOT NULL) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' rlike 'a'));
hex(concat('a' rlike 'a'))
31
create table t1 as select concat('a' IS NOT NULL) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(strcmp('a','b')));
hex(concat(strcmp('a','b')))
2D31
create table t1 as select concat(strcmp('a','b')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' like 'a'));
hex(concat('a' like 'a'))
31
create table t1 as select concat('a' like 'b') as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' between 'b' and 'c'));
hex(concat('a' between 'b' and 'c'))
30
create table t1 as select concat('a' between 'b' and 'c') as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat('a' in ('a','b')));
hex(concat('a' in ('a','b')))
31
create table t1 as select concat('a' in ('a','b')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(interval(23, 1, 15, 17, 30, 44, 200)));
hex(concat(interval(23, 1, 15, 17, 30, 44, 200)))
33
create table t1 as select concat(interval(23, 1, 15, 17, 30, 44, 200)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 (a varchar(10), fulltext key(a));
insert into t1 values ('a');
select hex(concat(match (a) against ('a'))) from t1;
hex(concat(match (a) against ('a')))
30
create table t2 as select concat(match (a) against ('a')) as a from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
select hex(ifnull(1,'a'));
hex(ifnull(1,'a'))
31
create table t1 as select ifnull(1,'a') as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ifnull(1,1)));
hex(concat(ifnull(1,1)))
31
create table t1 as select concat(ifnull(1,1)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(ifnull(1.1,1.1)));
hex(concat(ifnull(1.1,1.1)))
312E31
create table t1 as select concat(ifnull(1.1,1.1)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(4) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(if(1,'b',1));
hex(if(1,'b',1))
62
create table t1 as select if(1,'b',1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(if(1,1,'b'));
hex(if(1,1,'b'))
31
create table t1 as select if(1,1,'b') as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(if(1,1,1)));
hex(concat(if(1,1,1)))
31
create table t1 as select concat(if(1,1,1)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(nullif(1,2)));
hex(concat(nullif(1,2)))
31
create table t1 as select concat(nullif(1,2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)'))));
hex(concat(Dimension(GeomFromText('LINESTRING(0 0,10 10)'))))
31
create table t1 as select concat(Dimension(GeomFromText('LINSTRING(0 0,10 10)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))));
hex(concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))))
32
create table t1 as select concat(NumGeometries(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))));
hex(concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))))
32
create table t1 as select concat(NumPoints(MultiPointFromText('LINESTRING(0 0,10 10)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))));
hex(concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))))
30
create table t1 as select concat(SRID(MultiPointFromText('MULTIPOINT(0 0,10 10)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(NumInteriorRings(PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'))));
hex(concat(NumInteriorRings(PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'))))
31
create table t1 as select concat(NumInteriorRings(PolygonFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(IsEmpty(GeomFromText('POINT(1 1)'))));
hex(concat(IsEmpty(GeomFromText('POINT(1 1)'))))
30
create table t1 as select concat(IsEmpty(GeomFromText('Point(1 1)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(IsSimple(GeomFromText('POINT(1 1)'))));
hex(concat(IsSimple(GeomFromText('POINT(1 1)'))))
31
create table t1 as select concat(IsSimple(GeomFromText('Point(1 1)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))));
hex(concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))))
30
create table t1 as select concat(IsClosed(GeomFromText('LineString(1 1,2 2)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))));
hex(concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))))
31
create table t1 as select concat(Equals(GeomFromText('Point(1 1)'),GeomFromText('Point(1 1)'))) as c1;
drop table t1;
select hex(concat(x(GeomFromText('Point(1 2)'))));
hex(concat(x(GeomFromText('Point(1 2)'))))
31
create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(y(GeomFromText('Point(1 2)'))));
hex(concat(y(GeomFromText('Point(1 2)'))))
32
create table t1 as select concat(x(GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(GLength(GeomFromText('LineString(1 2,2 2)'))));
hex(concat(GLength(GeomFromText('LineString(1 2,2 2)'))))
31
create table t1 as select concat(GLength(GeomFromText('LineString(1 2, 2 2)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))));
hex(concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))))
31
create table t1 as select concat(Area(GeomFromText('Polygon((0 0,1 0,1 1,0 1,0 0))'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(23) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(GeometryType(GeomFromText('Point(1 2)'))));
hex(concat(GeometryType(GeomFromText('Point(1 2)'))))
504F494E54
create table t1 as select concat(GeometryType(GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(20) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(AsText(GeomFromText('Point(1 2)'))));
hex(concat(AsText(GeomFromText('Point(1 2)'))))
504F494E542831203229
create table t1 as select concat(AsText(GeomFromText('Point(1 2)'))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` longtext CHARACTER SET utf8
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(period_add(200902, 2)));
hex(concat(period_add(200902, 2)))
323030393034
create table t1 as select concat(period_add(200902, 2)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(period_diff(200902, 200802)));
hex(concat(period_diff(200902, 200802)))
3132
create table t1 as select concat(period_add(200902, 200802)) as c1;
Warnings:
Warning 1265 Data truncated for column 'c1' at row 1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(to_days(20090224)));
hex(concat(to_days(20090224)))
373333383237
create table t1 as select concat(to_days(20090224)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(6) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(dayofmonth(20090224)));
hex(concat(dayofmonth(20090224)))
3234
create table t1 as select concat(dayofmonth(20090224)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(dayofyear(20090224)));
hex(concat(dayofyear(20090224)))
3535
create table t1 as select concat(dayofyear(20090224)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(3) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(hour('10:11:12')));
hex(concat(hour('10:11:12')))
3130
create table t1 as select concat(hour('10:11:12')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(minute('10:11:12')));
hex(concat(minute('10:11:12')))
3131
create table t1 as select concat(minute('10:11:12')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(second('10:11:12')));
hex(concat(second('10:11:12')))
3132
create table t1 as select concat(second('10:11:12')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(quarter(20090224)));
hex(concat(quarter(20090224)))
31
create table t1 as select concat(quarter(20090224)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(week(20090224)));
hex(concat(week(20090224)))
38
create table t1 as select concat(week(20090224)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(yearweek(20090224)));
hex(concat(yearweek(20090224)))
323030393038
create table t1 as select concat(yearweek(20090224)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(6) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(year(20090224)));
hex(concat(year(20090224)))
32303039
create table t1 as select concat(year(20090224)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(4) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(weekday(20090224)));
hex(concat(weekday(20090224)))
31
create table t1 as select concat(weekday(20090224)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(dayofweek(20090224)));
hex(concat(dayofweek(20090224)))
33
create table t1 as select concat(dayofweek(20090224)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(unix_timestamp(20090224)));
hex(concat(unix_timestamp(20090224)))
31323335343232383030
create table t1 as select concat(unix_timestamp(20090224)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(11) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(time_to_sec('10:11:12')));
hex(concat(time_to_sec('10:11:12')))
3336363732
create table t1 as select concat(time_to_sec('10:11:12')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(extract(year from 20090702)));
hex(concat(extract(year from 20090702)))
32303039
create table t1 as select concat(extract(year from 20090702)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(4) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(microsecond('12:00:00.123456')));
hex(concat(microsecond('12:00:00.123456')))
313233343536
create table t1 as select concat(microsecond('12:00:00.123456')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(21) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(month(20090224)));
hex(concat(month(20090224)))
32
create table t1 as select concat(month(20090224)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(last_day('2003-02-05')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select c1, hex(c1) from t1;
c1 hex(c1)
2003-02-28 323030332D30322D3238
drop table t1;
create table t1 as select concat(from_days(730669)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select c1, hex(c1) from t1;
c1 hex(c1)
2000-07-03 323030302D30372D3033
drop table t1;
create table t1 as select concat(curdate()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(utc_date()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(curtime()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(8) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select repeat('a',20) as c1 limit 0;
set timestamp=1216359724;
insert into t1 values (current_date);
insert into t1 values (current_time);
select c1, hex(c1) from t1;
c1 hex(c1)
2008-07-18 323030382D30372D3138
08:42:04 30383A34323A3034
drop table t1;
create table t1 as select concat(utc_time()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(8) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(sec_to_time(2378)));
hex(concat(sec_to_time(2378)))
30303A33393A3338
create table t1 as select concat(sec_to_time(2378)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')));
hex(concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')))
32343A30303A3030
create table t1 as select concat(timediff('2001-01-02 00:00:00', '2001-01-01 00:00:00')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(maketime(10,11,12)));
hex(concat(maketime(10,11,12)))
31303A31313A3132
create table t1 as select concat(maketime(10,11,12)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(get_format(DATE,'USA'));
hex(get_format(DATE,'USA'))
256D2E25642E2559
create table t1 as select get_format(DATE,'USA') as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(17) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(concat(from_unixtime(1111885200)),4));
hex(left(concat(from_unixtime(1111885200)),4))
32303035
create table t1 as select concat(from_unixtime(1111885200)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(19) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')));
hex(concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')))
323030332D31322D33312032303A30303A3030
create table t1 as select concat(convert_tz('2004-01-01 12:00:00','+10:00','-6:00')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(19) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(date_add('2004-01-01 12:00:00', interval 1 day)));
hex(concat(date_add('2004-01-01 12:00:00', interval 1 day)))
323030342D30312D30322031323A30303A3030
create table t1 as select concat(date_add('2004-01-01 12:00:00', interval 1 day)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(29) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
c1
2004-01-02 12:00:00
drop table t1;
select hex(concat(makedate(2009,1)));
hex(concat(makedate(2009,1)))
323030392D30312D3031
create table t1 as select concat(makedate(2009,1)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
c1
2009-01-01
drop table t1;
create table t1 as select concat(now()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(19) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(utc_timestamp()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(19) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(sysdate()) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(19) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(addtime('00:00:00','11:22:33')));
hex(concat(addtime('00:00:00','11:22:33')))
31313A32323A3333
create table t1 as select concat(addtime('00:00:00','11:22:33')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(29) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(concat(subtime('23:59:59','11:22:33')));
hex(concat(subtime('23:59:59','11:22:33')))
31323A33373A3236
create table t1 as select concat(subtime('23:59:59','11:22:33')) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(29) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(elt(1,2,3));
hex(elt(1,2,3))
32
create table t1 as select elt(1,2,3) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(export_set(1,2,3,4,2));
hex(export_set(1,2,3,4,2))
323433
create table t1 as select export_set(1,2,3,4,2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(127) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(insert(1133,3,0,22));
hex(insert(1133,3,0,22))
313132323333
create table t1 as select insert(1133,3,0,22) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(lcase(123));
hex(lcase(123))
313233
create table t1 as select lcase(123) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(left(123,1));
hex(left(123,1))
31
create table t1 as select left(123,1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(lower(123));
hex(lower(123))
313233
create table t1 as select lower(123) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(lpad(1,2,0));
hex(lpad(1,2,0))
3031
create table t1 as select lpad(1,2,0) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(ltrim(1));
hex(ltrim(1))
31
create table t1 as select ltrim(1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(mid(1,1,1));
hex(mid(1,1,1))
31
create table t1 as select mid(1,1,1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(repeat(1,2));
hex(repeat(1,2))
3131
create table t1 as select repeat(1,2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(replace(1,1,2));
hex(replace(1,1,2))
32
create table t1 as select replace(1,1,2) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(reverse(12));
hex(reverse(12))
3231
create table t1 as select reverse(12) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(right(123,1));
hex(right(123,1))
33
create table t1 as select right(123,1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(rpad(1,2,0));
hex(rpad(1,2,0))
3130
create table t1 as select rpad(1,2,0) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(rtrim(1));
hex(rtrim(1))
31
create table t1 as select rtrim(1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(soundex(1));
hex(soundex(1))
create table t1 as select soundex(1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(4) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(substring(1,1,1));
hex(substring(1,1,1))
31
create table t1 as select substring(1,1,1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(trim(1));
hex(trim(1))
31
create table t1 as select trim(1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(ucase(1));
hex(ucase(1))
31
create table t1 as select ucase(1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
select hex(upper(1));
hex(upper(1))
31
create table t1 as select upper(1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select repeat(' ', 64) as a limit 0;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values ("1.1"), ("2.1");
select a, hex(a) from t1;
a hex(a)
1.1 312E31
2.1 322E31
update t1 set a= a + 0.1;
select a, hex(a) from t1;
a hex(a)
1.2000000000000002 312E32303030303030303030303030303032
2.2 322E32
drop table t1;
create table t1 (a tinyint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
31
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(4) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a tinyint zerofill);
insert into t1 values (1), (10), (100);
select hex(concat(a)), a from t1;
hex(concat(a)) a
303031 001
303130 010
313030 100
drop table t1;
create table t1 (a tinyint(4) zerofill);
insert into t1 values (1), (10), (100);
select hex(concat(a)), a from t1;
hex(concat(a)) a
30303031 0001
30303130 0010
30313030 0100
drop table t1;
create table t1 (a decimal(10,2));
insert into t1 values (123.45);
select hex(concat(a)) from t1;
hex(concat(a))
3132332E3435
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(12) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a smallint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
31
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(6) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a smallint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a)) a
3030303031 00001
3030303130 00010
3030313030 00100
3031303030 01000
3130303030 10000
drop table t1;
create table t1 (a mediumint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
31
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(9) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a mediumint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a)) a
3030303030303031 00000001
3030303030303130 00000010
3030303030313030 00000100
3030303031303030 00001000
3030303130303030 00010000
drop table t1;
create table t1 (a int);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
31
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(11) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a int zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a)) a
30303030303030303031 0000000001
30303030303030303130 0000000010
30303030303030313030 0000000100
30303030303031303030 0000001000
30303030303130303030 0000010000
drop table t1;
create table t1 (a bigint);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
31
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(20) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a bigint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
select hex(concat(a)), a from t1;
hex(concat(a)) a
3030303030303030303030303030303030303031 00000000000000000001
3030303030303030303030303030303030303130 00000000000000000010
3030303030303030303030303030303030313030 00000000000000000100
3030303030303030303030303030303031303030 00000000000000001000
3030303030303030303030303030303130303030 00000000000000010000
drop table t1;
create table t1 (a float);
insert into t1 values (123.456);
select hex(concat(a)) from t1;
hex(concat(a))
3132332E343536
select concat(a) from t1;
concat(a)
123.456
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(12) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a float zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
select hex(concat(a)), a from t1;
hex(concat(a)) a
303030303030303030312E31 0000000001.1
303030303030303031302E31 0000000010.1
303030303030303130302E31 0000000100.1
303030303030313030302E31 0000001000.1
303030303031303030302E31 0000010000.1
drop table t1;
create table t1 (a double);
insert into t1 values (123.456);
select hex(concat(a)) from t1;
hex(concat(a))
3132332E343536
select concat(a) from t1;
concat(a)
123.456
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(22) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a double zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
select hex(concat(a)), a from t1;
hex(concat(a)) a
30303030303030303030303030303030303030312E31 00000000000000000001.1
30303030303030303030303030303030303031302E31 00000000000000000010.1
30303030303030303030303030303030303130302E31 00000000000000000100.1
30303030303030303030303030303030313030302E31 00000000000000001000.1
30303030303030303030303030303031303030302E31 00000000000000010000.1
drop table t1;
create table t1 (a year(2));
Warnings:
Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead.
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
32303031
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(4) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a year);
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
32303031
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(4) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a bit(64));
insert into t1 values (1);
select hex(concat(a)) from t1;
hex(concat(a))
0000000000000001
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varbinary(64) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert into t1 values (0);
insert into t1 values (20010203040506);
insert into t1 values (19800203040506);
insert into t1 values ('2001-02-03 04:05:06');
select hex(concat(a)) from t1;
hex(concat(a))
303030302D30302D30302030303A30303A3030
323030312D30322D30332030343A30353A3036
313938302D30322D30332030343A30353A3036
323030312D30322D30332030343A30353A3036
select concat(a) from t1;
concat(a)
0000-00-00 00:00:00
2001-02-03 04:05:06
1980-02-03 04:05:06
2001-02-03 04:05:06
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(19) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a date);
insert into t1 values ('2001-02-03');
insert into t1 values (20010203);
select hex(concat(a)) from t1;
hex(concat(a))
323030312D30322D3033
323030312D30322D3033
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a time);
insert into t1 values (1);
insert into t1 values ('01:02:03');
select hex(concat(a)) from t1;
hex(concat(a))
30303A30303A3031
30313A30323A3033
select concat(a) from t1;
concat(a)
00:00:01
01:02:03
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a datetime);
insert into t1 values ('2001-02-03 04:05:06');
insert into t1 values (20010203040506);
select hex(concat(a)) from t1;
hex(concat(a))
323030312D30322D30332030343A30353A3036
323030312D30322D30332030343A30353A3036
create table t2 as select concat(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`concat(a)` varchar(19) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2;
create table t1 (a tinyint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(4) YES NULL
select hex(a) from v1;
hex(a)
31
drop table t1;
drop view v1;
create table t1 (a tinyint zerofill);
insert into t1 values (1), (10), (100);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(3) YES NULL
select hex(a) from v1;
hex(a)
303031
303130
313030
drop table t1;
drop view v1;
create table t1 (a tinyint(30) zerofill);
insert into t1 values (1), (10), (100);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(30) YES NULL
select hex(a) from v1;
hex(a)
303030303030303030303030303030303030303030303030303030303031
303030303030303030303030303030303030303030303030303030303130
303030303030303030303030303030303030303030303030303030313030
drop table t1;
drop view v1;
create table t1 (a decimal(10,2));
insert into t1 values (123.45);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(12) YES NULL
select hex(a) from v1;
hex(a)
3132332E3435
drop table t1;
drop view v1;
create table t1 (a smallint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(6) YES NULL
select hex(a) from v1;
hex(a)
31
drop table t1;
drop view v1;
create table t1 (a smallint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(5) YES NULL
select hex(a) from v1;
hex(a)
3030303031
3030303130
3030313030
3031303030
3130303030
drop table t1;
drop view v1;
create table t1 (a mediumint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(9) YES NULL
select hex(a) from v1;
hex(a)
31
drop table t1;
drop view v1;
create table t1 (a mediumint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(8) YES NULL
select hex(a) from v1;
hex(a)
3030303030303031
3030303030303130
3030303030313030
3030303031303030
3030303130303030
drop table t1;
drop view v1;
create table t1 (a int);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(11) YES NULL
select hex(a) from v1;
hex(a)
31
drop table t1;
drop view v1;
create table t1 (a int zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(10) YES NULL
select hex(a) from v1;
hex(a)
30303030303030303031
30303030303030303130
30303030303030313030
30303030303031303030
30303030303130303030
drop table t1;
drop view v1;
create table t1 (a bigint);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(20) YES NULL
select hex(a) from v1;
hex(a)
31
drop table t1;
drop view v1;
create table t1 (a bigint zerofill);
insert into t1 values (1), (10), (100), (1000), (10000);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(20) YES NULL
select hex(a) from v1;
hex(a)
3030303030303030303030303030303030303031
3030303030303030303030303030303030303130
3030303030303030303030303030303030313030
3030303030303030303030303030303031303030
3030303030303030303030303030303130303030
drop table t1;
drop view v1;
create table t1 (a float);
insert into t1 values (123.456);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(12) YES NULL
select hex(a) from v1;
hex(a)
3132332E343536
drop table t1;
drop view v1;
create table t1 (a float zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(12) YES NULL
select hex(a) from v1;
hex(a)
303030303030303030312E31
303030303030303031302E31
303030303030303130302E31
303030303030313030302E31
303030303031303030302E31
drop table t1;
drop view v1;
create table t1 (a double);
insert into t1 values (123.456);
select concat(a) from t1;
concat(a)
123.456
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(22) YES NULL
select hex(a) from v1;
hex(a)
3132332E343536
drop table t1;
drop view v1;
create table t1 (a double zerofill);
insert into t1 values (1.1), (10.1), (100.1), (1000.1), (10000.1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(22) YES NULL
select hex(a) from v1;
hex(a)
30303030303030303030303030303030303030312E31
30303030303030303030303030303030303031302E31
30303030303030303030303030303030303130302E31
30303030303030303030303030303030313030302E31
30303030303030303030303030303031303030302E31
drop table t1;
drop view v1;
create table t1 (a year(2));
Warnings:
Warning 1818 YEAR(2) column type is deprecated. Creating YEAR(4) column instead.
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(4) YES NULL
select hex(a) from v1;
hex(a)
32303031
drop table t1;
drop view v1;
create table t1 (a year);
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(4) YES NULL
select hex(a) from v1;
hex(a)
32303031
drop table t1;
drop view v1;
create table t1 (a bit(64));
insert into t1 values (1);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varbinary(64) YES NULL
select hex(a) from v1;
hex(a)
0000000000000001
drop table t1;
drop view v1;
create table t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert into t1 values (0);
insert into t1 values (20010203040506);
insert into t1 values (19800203040506);
insert into t1 values ('2001-02-03 04:05:06');
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(19) NO
select hex(a) from v1;
hex(a)
303030302D30302D30302030303A30303A3030
323030312D30322D30332030343A30353A3036
313938302D30322D30332030343A30353A3036
323030312D30322D30332030343A30353A3036
drop table t1;
drop view v1;
create table t1 (a date);
insert into t1 values ('2001-02-03');
insert into t1 values (20010203);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(10) YES NULL
select hex(a) from v1;
hex(a)
323030312D30322D3033
323030312D30322D3033
drop table t1;
drop view v1;
create table t1 (a time);
insert into t1 values (1);
insert into t1 values ('01:02:03');
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(10) YES NULL
select hex(a) from v1;
hex(a)
30303A30303A3031
30313A30323A3033
drop table t1;
drop view v1;
create table t1 (a datetime);
insert into t1 values ('2001-02-03 04:05:06');
insert into t1 values (20010203040506);
create view v1(a) as select concat(a) from t1;
show columns from v1;
Field Type Null Key Default Extra
a varchar(19) YES NULL
select hex(a) from v1;
hex(a)
323030312D30322D30332030343A30353A3036
323030312D30322D30332030343A30353A3036
drop table t1;
drop view v1;
create function f1 (par1 int) returns int
begin
return concat(par1);
end|
set @a= f1(1);
select hex(@a);
hex(@a)
1
select hex(concat(f1(1)));
hex(concat(f1(1)))
31
create table t1 as select f1(1) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(1)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create view v1 as select concat(f1(1)) as c1;
show columns from v1;
Field Type Null Key Default Extra
c1 varchar(11) YES NULL
drop table t1;
drop view v1;
drop function f1;
create function f1 (par1 decimal(18,2)) returns decimal(18,2)
begin
return concat(par1);
end|
set @a= f1(123.45);
select hex(@a);
hex(@a)
7B
select hex(concat(f1(123.45)));
hex(concat(f1(123.45)))
3132332E3435
create table t1 as select f1(123.45) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` decimal(18,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(123.45)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create view v1 as select concat(f1(123.45)) as c1;
show columns from v1;
Field Type Null Key Default Extra
c1 varchar(20) YES NULL
drop table t1;
drop view v1;
drop function f1;
create function f1 (par1 float) returns float
begin
return concat(par1);
end|
set @a= f1(123.45);
select hex(@a);
hex(@a)
7B
select hex(concat(f1(123.45)));
hex(concat(f1(123.45)))
3132332E3435
create table t1 as select f1(123.45) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(123.45)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create view v1 as select concat(f1(123.45)) as c1;
show columns from v1;
Field Type Null Key Default Extra
c1 varchar(12) YES NULL
drop table t1;
drop view v1;
drop function f1;
create function f1 (par1 date) returns date
begin
return concat(par1);
end|
set @a= f1(cast('2001-01-02' as date));
select hex(@a);
hex(@a)
323030312D30312D3032
select hex(concat(f1(cast('2001-01-02' as date))));
hex(concat(f1(cast('2001-01-02' as date))))
323030312D30312D3032
create table t1 as select f1(cast('2001-01-02' as date)) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 as select concat(f1(cast('2001-01-02' as date))) as c1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create view v1 as select concat(f1(cast('2001-01-02' as date))) as c1;
show columns from v1;
Field Type Null Key Default Extra
c1 varchar(10) YES NULL
drop table t1;
drop view v1;
drop function f1;
#
# End of WL#2649 Number-to-string conversions
#
#
# Bug#54668 User variable assignments get wrong type
#
SET @x=md5('a');
SELECT charset(@x), collation(@x);
charset(@x) collation(@x)
utf8 utf8_general_ci
SET @x=old_password('a');
Warnings:
Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead
SELECT charset(@x), collation(@x);
charset(@x) collation(@x)
utf8 utf8_general_ci
SET @x=password('a');
SELECT charset(@x), collation(@x);
charset(@x) collation(@x)
utf8 utf8_general_ci
SET @x=sha('a');
SELECT charset(@x), collation(@x);
charset(@x) collation(@x)
utf8 utf8_general_ci
SET @x=sha1('a');
SELECT charset(@x), collation(@x);
charset(@x) collation(@x)
utf8 utf8_general_ci
SET @x=astext(point(1,2));
SELECT charset(@x), collation(@x);
charset(@x) collation(@x)
utf8 utf8_general_ci
SET @x=aswkt(point(1,2));
SELECT charset(@x), collation(@x);
charset(@x) collation(@x)
utf8 utf8_general_ci
#
# Bug#54916 GROUP_CONCAT + IFNULL truncates output
#
SELECT @@collation_connection;
@@collation_connection
utf8_general_ci
CREATE TABLE t1 (a MEDIUMINT NULL) ENGINE=MYISAM;
INSERT INTO t1 VALUES (1234567);
SELECT GROUP_CONCAT(IFNULL(a,'')) FROM t1;
GROUP_CONCAT(IFNULL(a,''))
1234567
SELECT GROUP_CONCAT(IF(a,a,'')) FROM t1;
GROUP_CONCAT(IF(a,a,''))
1234567
SELECT GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) FROM t1;
GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END)
1234567
SELECT COALESCE(a,'') FROM t1 GROUP BY 1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def COALESCE(a,'') 253 27 7 Y 0 31 33
COALESCE(a,'')
1234567
# All columns must be VARCHAR(9) with the same length:
CREATE TABLE t2 AS
SELECT
CONCAT(a),
IFNULL(a,''),
IF(a,a,''),
CASE WHEN a THEN a ELSE '' END,
COALESCE(a,'')
FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`CONCAT(a)` varchar(9) CHARACTER SET utf8 DEFAULT NULL,
`IFNULL(a,'')` varchar(9) CHARACTER SET utf8 NOT NULL DEFAULT '',
`IF(a,a,'')` varchar(9) CHARACTER SET utf8 DEFAULT NULL,
`CASE WHEN a THEN a ELSE '' END` varchar(9) CHARACTER SET utf8 DEFAULT NULL,
`COALESCE(a,'')` varchar(9) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT CONCAT_WS(1,2,3) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`CONCAT_WS(1,2,3)` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT INSERT(1133,3,0,22) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`INSERT(1133,3,0,22)` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LCASE(a) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`LCASE(a)` varchar(9) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT UCASE(a) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`UCASE(a)` varchar(9) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT REPEAT(1,2) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`REPEAT(1,2)` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LEFT(123,2) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`LEFT(123,2)` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT RIGHT(123,2) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`RIGHT(123,2)` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT LTRIM(123) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`LTRIM(123)` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT RTRIM(123) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`RTRIM(123)` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT ELT(1,111,222,333) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`ELT(1,111,222,333)` varchar(3) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT REPLACE(111,2,3) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`REPLACE(111,2,3)` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT SUBSTRING_INDEX(111,111,1) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`SUBSTRING_INDEX(111,111,1)` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT MAKE_SET(111,222,3) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`MAKE_SET(111,222,3)` varchar(5) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT SOUNDEX(1) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`SOUNDEX(1)` varchar(4) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
CREATE TABLE t2 AS SELECT EXPORT_SET(1,'Y','N','',8);
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`EXPORT_SET(1,'Y','N','',8)` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
#
# End of Bug#54916
#
#
# WL#5510 Functions to_base64 and from_base64
#
CREATE TABLE t1 AS SELECT TO_BASE64('test') AS to_base64;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`to_base64` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT to_base64, LENGTH(to_base64), HEX(to_base64) FROM t1;
to_base64 LENGTH(to_base64) HEX(to_base64)
dGVzdA== 8 6447567A64413D3D
CREATE TABLE t2 AS SELECT FROM_BASE64(to_base64) AS from_base64 FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`from_base64` varbinary(36) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SELECT CAST(from_base64 AS CHAR), LENGTH(from_base64), HEX(from_base64) FROM t2;
CAST(from_base64 AS CHAR) LENGTH(from_base64) HEX(from_base64)
test 4 74657374
DROP TABLE t2;
DROP TABLE t1;
#
# Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
#
SELECT @@collation_connection;
@@collation_connection
utf8_general_ci
CREATE TABLE t1 (
id INT(11) DEFAULT NULL,
date_column DATE DEFAULT NULL,
KEY(date_column));
INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range date_column date_column 4 NULL 1 Using index condition
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range date_column date_column 6 NULL 1 Using index condition
DROP TABLE t1;
#
# Bug #31384 DATE_ADD() and DATE_SUB() return binary data
#
SELECT @@collation_connection, @@character_set_results;
@@collation_connection @@character_set_results
utf8_general_ci utf8
CREATE TABLE t1 AS
SELECT
DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1,
DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`field_str1` varchar(29) CHARACTER SET utf8 DEFAULT NULL,
`field1_str2` varchar(29) CHARACTER SET utf8 DEFAULT NULL,
`field_date` date DEFAULT NULL,
`field_datetime` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
SELECT
DATE_SUB('2007-08-03', INTERVAL 1 DAY) AS field_str1,
DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2,
DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date,
DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def field_str1 254 87 10 Y 0 31 33
def field1_str2 254 87 19 Y 0 31 33
def field_date 10 10 10 Y 128 0 63
def field_datetime 12 19 19 Y 128 0 63
field_str1 field1_str2 field_date field_datetime
2007-08-02 2007-08-03 17:32:00 2007-08-02 2007-08-03 17:32:00
SELECT
HEX(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1,
HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2,
HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date,
HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime;
field_str1 field1_str2 field_date field_datetime
323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030
#
# Bug#11926811 / Bug#60625 Illegal mix of collations
#
SELECT @@collation_connection;
@@collation_connection
utf8_general_ci
CREATE PROCEDURE p1()
BEGIN
DECLARE v_LastPaymentDate DATETIME DEFAULT NULL;
SELECT v_LastPaymentDate < NOW();
EXPLAIN EXTENDED SELECT v_LastPaymentDate < NOW();
SHOW WARNINGS;
EXPLAIN EXTENDED SELECT CONCAT(v_LastPaymentDate, NOW());
END//
CALL p1;
v_LastPaymentDate < NOW()
NULL
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Level Code Message
Note 1003 /* select#1 */ select (v_LastPaymentDate@0 < now()) AS `v_LastPaymentDate < NOW()`
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 /* select#1 */ select concat(v_LastPaymentDate@0,now()) AS `CONCAT(v_LastPaymentDate, NOW())`
DROP PROCEDURE p1;
#
# Bug#52159 returning time type from function and empty left join causes debug assertion
#
CREATE FUNCTION f1() RETURNS TIME RETURN 1;
CREATE TABLE t1 (b INT);
INSERT INTO t1 VALUES (0);
SELECT f1() FROM t1 LEFT JOIN (SELECT 1 AS a FROM t1 LIMIT 0) AS d ON 1 GROUP BY a;
f1()
00:00:01
DROP FUNCTION f1;
DROP TABLE t1;
SET NAMES latin1;
SET sql_mode='';
CREATE TABLE t1(a char(215) CHARACTER SET utf8 NOT NULL DEFAULT '', KEY(a));
INSERT INTO t1 VALUES ();
SELECT maketime(`a`,`a`,`a`) FROM t1 GROUP BY 1;
maketime(`a`,`a`,`a`)
00:00:00.000000
DROP TABLE t1;
SET sql_mode=default;
#
# Bug#57687 crash when reporting duplicate group_key error and utf8
# Make sure to modify this when Bug#58081 is fixed.
#
SET NAMES utf8;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (0), (0), (1), (0), (0);
SELECT COUNT(*) FROM t1, t1 t2
GROUP BY INSERT('', t2.a, t1.a, (@@global.max_binlog_size));
COUNT(*)
25
DROP TABLE t1;
#
# Bug#11764503 (Bug#57341) Query in EXPLAIN EXTENDED shows wrong characters
#
SET NAMES latin1;
EXPLAIN EXTENDED SELECT 'abcdÁÂÃÄÅ', _latin1'abcdÁÂÃÄÅ', _utf8'abcdÁÂÃÄÅ' AS u;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 /* select#1 */ select 'abcdÁÂÃÄÅ' AS `abcdÁÂÃÄÅ`,_latin1'abcd\xC3\x81\xC3\x82\xC3\x83\xC3\x84\xC3\x85' AS `abcdÁÂÃÄÅ`,_utf8'abcd\xC3\x81\xC3\x82\xC3\x83\xC3\x84\xC3\x85' AS `u`
SET NAMES utf8;
EXPLAIN EXTENDED SELECT 'abcdÁÂÃÄÅ', _latin1'abcdÁÂÃÄÅ', _utf8'abcdÁÂÃÄÅ';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 /* select#1 */ select 'abcdÁÂÃÄÅ' AS `abcdÁÂÃÄÅ`,_latin1'abcd\xC3\x81\xC3\x82\xC3\x83\xC3\x84\xC3\x85' AS `abcdÃÂÃÄÅ`,_utf8'abcd\xC3\x81\xC3\x82\xC3\x83\xC3\x84\xC3\x85' AS `abcdÁÂÃÄÅ`
#
# Bug#11750518 41090: ORDER BY TRUNCATES GROUP_CONCAT RESULT
#
SET NAMES utf8;
SELECT id, CHAR_LENGTH(GROUP_CONCAT(body)) AS l
FROM (SELECT 'a' AS id, REPEAT('foo bar', 100) AS body
UNION ALL
SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1
GROUP BY id
ORDER BY l DESC;
id l
a 1024
Warnings:
Warning 1260 Row 2 was cut by GROUP_CONCAT()
#
# End of 5.5 tests
#
#
# Start of 5.6 tests
#
#
# WL#3664 WEIGHT_STRING
#
set names utf8;
select @@collation_connection;
@@collation_connection
utf8_general_ci
select hex(weight_string('a'));
hex(weight_string('a'))
0041
select hex(weight_string('A'));
hex(weight_string('A'))
0041
select hex(weight_string('abc'));
hex(weight_string('abc'))
004100420043
select hex(weight_string('abc' as char(2)));
hex(weight_string('abc' as char(2)))
00410042
select hex(weight_string('abc' as char(3)));
hex(weight_string('abc' as char(3)))
004100420043
select hex(weight_string('abc' as char(5)));
hex(weight_string('abc' as char(5)))
00410042004300200020
select hex(weight_string('abc', 1, 2, 0xC0));
hex(weight_string('abc', 1, 2, 0xC0))
00
select hex(weight_string('abc', 2, 2, 0xC0));
hex(weight_string('abc', 2, 2, 0xC0))
0041
select hex(weight_string('abc', 3, 2, 0xC0));
hex(weight_string('abc', 3, 2, 0xC0))
004100
select hex(weight_string('abc', 4, 2, 0xC0));
hex(weight_string('abc', 4, 2, 0xC0))
00410042
select hex(weight_string('abc', 5, 2, 0xC0));
hex(weight_string('abc', 5, 2, 0xC0))
0041004200
select hex(weight_string('abc',25, 2, 0xC0));
hex(weight_string('abc',25, 2, 0xC0))
00410042002000200020002000200020002000200020002000
select hex(weight_string('abc', 1, 3, 0xC0));
hex(weight_string('abc', 1, 3, 0xC0))
00
select hex(weight_string('abc', 2, 3, 0xC0));
hex(weight_string('abc', 2, 3, 0xC0))
0041
select hex(weight_string('abc', 3, 3, 0xC0));
hex(weight_string('abc', 3, 3, 0xC0))
004100
select hex(weight_string('abc', 4, 3, 0xC0));
hex(weight_string('abc', 4, 3, 0xC0))
00410042
select hex(weight_string('abc', 5, 3, 0xC0));
hex(weight_string('abc', 5, 3, 0xC0))
0041004200
select hex(weight_string('abc',25, 3, 0xC0));
hex(weight_string('abc',25, 3, 0xC0))
00410042004300200020002000200020002000200020002000
select hex(weight_string('abc', 1, 4, 0xC0));
hex(weight_string('abc', 1, 4, 0xC0))
00
select hex(weight_string('abc', 2, 4, 0xC0));
hex(weight_string('abc', 2, 4, 0xC0))
0041
select hex(weight_string('abc', 3, 4, 0xC0));
hex(weight_string('abc', 3, 4, 0xC0))
004100
select hex(weight_string('abc', 4, 4, 0xC0));
hex(weight_string('abc', 4, 4, 0xC0))
00410042
select hex(weight_string('abc', 5, 4, 0xC0));
hex(weight_string('abc', 5, 4, 0xC0))
0041004200
select hex(weight_string('abc',25, 4, 0xC0));
hex(weight_string('abc',25, 4, 0xC0))
00410042004300200020002000200020002000200020002000
select @@collation_connection;
@@collation_connection
utf8_general_ci
select hex(weight_string(cast(_latin1 0x80 as char)));
hex(weight_string(cast(_latin1 0x80 as char)))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char)));
hex(weight_string(cast(_latin1 0x808080 as char)))
20AC20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(2)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(2)))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(3)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(3)))
20AC20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(5)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(5)))
20AC20AC20AC00200020
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0))
20AC20AC00
select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0))
20AC20AC002000200020002000200020002000200020002000
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0))
20AC20AC20
select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0))
20AC20AC20AC00200020002000200020002000200020002000
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0))
20AC20AC20
select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0))
20AC20AC20AC00200020002000200020002000200020002000
select @@collation_connection;
@@collation_connection
utf8_general_ci
select hex(weight_string('a' LEVEL 1));
hex(weight_string('a' LEVEL 1))
0041
select hex(weight_string('A' LEVEL 1));
hex(weight_string('A' LEVEL 1))
0041
select hex(weight_string('abc' LEVEL 1));
hex(weight_string('abc' LEVEL 1))
004100420043
select hex(weight_string('abc' as char(2) LEVEL 1));
hex(weight_string('abc' as char(2) LEVEL 1))
00410042
select hex(weight_string('abc' as char(3) LEVEL 1));
hex(weight_string('abc' as char(3) LEVEL 1))
004100420043
select hex(weight_string('abc' as char(5) LEVEL 1));
hex(weight_string('abc' as char(5) LEVEL 1))
00410042004300200020
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
20002000430042004100
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
FFBEFFBDFFBCFFDFFFDF
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
DFFFDFFFBCFFBDFFBEFF
set @@collation_connection=utf8_bin;
select @@collation_connection;
@@collation_connection
utf8_bin
select hex(weight_string('a'));
hex(weight_string('a'))
0061
select hex(weight_string('A'));
hex(weight_string('A'))
0041
select hex(weight_string('abc'));
hex(weight_string('abc'))
006100620063
select hex(weight_string('abc' as char(2)));
hex(weight_string('abc' as char(2)))
00610062
select hex(weight_string('abc' as char(3)));
hex(weight_string('abc' as char(3)))
006100620063
select hex(weight_string('abc' as char(5)));
hex(weight_string('abc' as char(5)))
00610062006300200020
select hex(weight_string('abc', 1, 2, 0xC0));
hex(weight_string('abc', 1, 2, 0xC0))
00
select hex(weight_string('abc', 2, 2, 0xC0));
hex(weight_string('abc', 2, 2, 0xC0))
0061
select hex(weight_string('abc', 3, 2, 0xC0));
hex(weight_string('abc', 3, 2, 0xC0))
006100
select hex(weight_string('abc', 4, 2, 0xC0));
hex(weight_string('abc', 4, 2, 0xC0))
00610062
select hex(weight_string('abc', 5, 2, 0xC0));
hex(weight_string('abc', 5, 2, 0xC0))
0061006200
select hex(weight_string('abc',25, 2, 0xC0));
hex(weight_string('abc',25, 2, 0xC0))
00610062002000200020002000200020002000200020002000
select hex(weight_string('abc', 1, 3, 0xC0));
hex(weight_string('abc', 1, 3, 0xC0))
00
select hex(weight_string('abc', 2, 3, 0xC0));
hex(weight_string('abc', 2, 3, 0xC0))
0061
select hex(weight_string('abc', 3, 3, 0xC0));
hex(weight_string('abc', 3, 3, 0xC0))
006100
select hex(weight_string('abc', 4, 3, 0xC0));
hex(weight_string('abc', 4, 3, 0xC0))
00610062
select hex(weight_string('abc', 5, 3, 0xC0));
hex(weight_string('abc', 5, 3, 0xC0))
0061006200
select hex(weight_string('abc',25, 3, 0xC0));
hex(weight_string('abc',25, 3, 0xC0))
00610062006300200020002000200020002000200020002000
select hex(weight_string('abc', 1, 4, 0xC0));
hex(weight_string('abc', 1, 4, 0xC0))
00
select hex(weight_string('abc', 2, 4, 0xC0));
hex(weight_string('abc', 2, 4, 0xC0))
0061
select hex(weight_string('abc', 3, 4, 0xC0));
hex(weight_string('abc', 3, 4, 0xC0))
006100
select hex(weight_string('abc', 4, 4, 0xC0));
hex(weight_string('abc', 4, 4, 0xC0))
00610062
select hex(weight_string('abc', 5, 4, 0xC0));
hex(weight_string('abc', 5, 4, 0xC0))
0061006200
select hex(weight_string('abc',25, 4, 0xC0));
hex(weight_string('abc',25, 4, 0xC0))
00610062006300200020002000200020002000200020002000
select @@collation_connection;
@@collation_connection
utf8_bin
select hex(weight_string(cast(_latin1 0x80 as char)));
hex(weight_string(cast(_latin1 0x80 as char)))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char)));
hex(weight_string(cast(_latin1 0x808080 as char)))
20AC20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(2)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(2)))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(3)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(3)))
20AC20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char) as char(5)));
hex(weight_string(cast(_latin1 0x808080 as char) as char(5)))
20AC20AC20AC00200020
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0))
20AC20AC00
select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0))
20AC20AC002000200020002000200020002000200020002000
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0))
20AC20AC20
select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0))
20AC20AC20AC00200020002000200020002000200020002000
select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0))
20
select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0))
20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0))
20AC20
select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0))
20AC20AC
select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0))
20AC20AC20
select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0));
hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0))
20AC20AC20AC00200020002000200020002000200020002000
select @@collation_connection;
@@collation_connection
utf8_bin
select hex(weight_string('a' LEVEL 1));
hex(weight_string('a' LEVEL 1))
0061
select hex(weight_string('A' LEVEL 1));
hex(weight_string('A' LEVEL 1))
0041
select hex(weight_string('abc' LEVEL 1));
hex(weight_string('abc' LEVEL 1))
006100620063
select hex(weight_string('abc' as char(2) LEVEL 1));
hex(weight_string('abc' as char(2) LEVEL 1))
00610062
select hex(weight_string('abc' as char(3) LEVEL 1));
hex(weight_string('abc' as char(3) LEVEL 1))
006100620063
select hex(weight_string('abc' as char(5) LEVEL 1));
hex(weight_string('abc' as char(5) LEVEL 1))
00610062006300200020
select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 REVERSE))
20002000630062006100
select hex(weight_string('abc' as char(5) LEVEL 1 DESC));
hex(weight_string('abc' as char(5) LEVEL 1 DESC))
FF9EFF9DFF9CFFDFFFDF
select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE));
hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE))
DFFFDFFF9CFF9DFF9EFF
#
# Checking strnxfrm() with odd length
#
set max_sort_length=5;
select @@max_sort_length;
@@max_sort_length
5
create table t1 (a varchar(128) character set utf8 collate utf8_general_ci);
insert into t1 values ('a'),('b'),('c');
select * from t1 order by a;
a
a
b
c
alter table t1 modify a varchar(128) character set utf8 collate utf8_bin;
select * from t1 order by a;
a
a
b
c
drop table t1;
set max_sort_length=default;
#
# Bugs#12635232: VALGRIND WARNINGS: IS_IPV6, IS_IPV4, INET6_ATON,
# INET6_NTOA + MULTIBYTE CHARSET.
#
SET NAMES utf8;
SELECT is_ipv4(inet_ntoa('1'));
is_ipv4(inet_ntoa('1'))
1
SELECT is_ipv6(inet_ntoa('1'));
is_ipv6(inet_ntoa('1'))
0
SELECT inet6_aton(inet_ntoa('1'));
inet6_aton(inet_ntoa('1'))
SELECT inet6_ntoa(inet_ntoa('1'));
inet6_ntoa(inet_ntoa('1'))
NULL
#
# Bug#14040277 UNINITIALIZED VALUE REFERENCED IN STR_TO_IPV6
#
SELECT inet6_aton(soundex('a'));
inet6_aton(soundex('a'))
NULL
#
# End of 5.6 tests
#