1
create table t1 (s1 binary(3));
2
insert into t1 values (0x61), (0x6120), (0x612020);
3
select hex(s1) from t1;
9
create table t1 (s1 binary(2), s2 varbinary(2));
10
insert into t1 values (0x4100,0x4100);
11
select length(concat('*',s1,'*',s2,'*')) from t1;
12
length(concat('*',s1,'*',s2,'*'))
15
insert into t1 values (0x4120,0x4120);
16
select length(concat('*',s1,'*',s2,'*')) from t1;
17
length(concat('*',s1,'*',s2,'*'))
20
create table t1 (s1 varbinary(20), s2 varbinary(20));
23
t1 CREATE TABLE `t1` (
24
`s1` varbinary(20) DEFAULT NULL,
25
`s2` varbinary(20) DEFAULT NULL
26
) ENGINE=MyISAM DEFAULT CHARSET=latin1
27
insert into t1 values (0x41,0x4100),(0x41,0x4120),(0x4100,0x4120);
28
select hex(s1), hex(s2) from t1;
33
select count(*) from t1 where s1 < s2;
37
create table t1 (s1 varbinary(2), s2 varchar(1));
38
insert into t1 values (0x41,'a'), (0x4100,'b'), (0x41,'c'), (0x4100,'d');
39
select hex(s1),s2 from t1 order by s1,s2;
46
create table t1 (s1 binary(2) primary key);
47
insert into t1 values (0x01);
48
insert into t1 values (0x0120);
49
insert into t1 values (0x0100);
50
ERROR 23000: Duplicate entry '' for key 'PRIMARY'
51
select hex(s1) from t1 order by s1;
55
select hex(s1) from t1 where s1=0x01;
57
select hex(s1) from t1 where s1=0x0120;
60
select hex(s1) from t1 where s1=0x0100;
63
select count(distinct s1) from t1;
66
alter table t1 drop primary key;
67
select hex(s1) from t1 where s1=0x01;
69
select hex(s1) from t1 where s1=0x0120;
72
select hex(s1) from t1 where s1=0x0100;
75
select count(distinct s1) from t1;
79
create table t1 (s1 varbinary(2) primary key);
80
insert into t1 values (0x01);
81
insert into t1 values (0x0120);
82
insert into t1 values (0x0100);
83
select hex(s1) from t1 order by s1;
88
select hex(s1) from t1 where s1=0x01;
91
select hex(s1) from t1 where s1=0x0120;
94
select hex(s1) from t1 where s1=0x0100;
97
select count(distinct s1) from t1;
100
alter table t1 drop primary key;
101
select hex(s1) from t1 where s1=0x01;
104
select hex(s1) from t1 where s1=0x0120;
107
select hex(s1) from t1 where s1=0x0100;
110
select count(distinct s1) from t1;
114
select hex(cast(0x10 as binary(2)));
115
hex(cast(0x10 as binary(2)))
117
create table t1 (b binary(2), vb varbinary(2));
118
insert into t1 values(0x4120, 0x4120);
119
insert into t1 values(0x412020, 0x412020);
121
Warning 1265 Data truncated for column 'b' at row 1
122
Warning 1265 Data truncated for column 'vb' at row 1
124
create table t1 (c char(2), vc varchar(2));
125
insert into t1 values(0x4120, 0x4120);
126
insert into t1 values(0x412020, 0x412020);
128
Note 1265 Data truncated for column 'c' at row 1
129
Note 1265 Data truncated for column 'vc' at row 1
131
set @old_sql_mode= @@sql_mode, sql_mode= 'traditional';
132
create table t1 (b binary(2), vb varbinary(2));
133
insert into t1 values(0x4120, 0x4120);
134
insert into t1 values(0x412020, NULL);
135
ERROR 22001: Data too long for column 'b' at row 1
136
insert into t1 values(NULL, 0x412020);
137
ERROR 22001: Data too long for column 'vb' at row 1
139
set @@sql_mode= @old_sql_mode;
140
create table t1(f1 int, f2 binary(2) not null, f3 char(2) not null);
141
insert into t1 set f1=1;
143
Warning 1364 Field 'f2' doesn't have a default value
144
Warning 1364 Field 'f3' doesn't have a default value
145
select hex(f2), hex(f3) from t1;