1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
|
drop table if exists t1;
DROP TABLE IF EXISTS t1;
SHOW COLLATION LIKE 'cp1250_czech_cs';
Collation Charset Id Default Compiled Sortlen
cp1250_czech_cs cp1250 34 Yes 2
SET @test_character_set= 'cp1250';
SET @test_collation= 'cp1250_general_ci';
SET @safe_character_set_server= @@character_set_server;
SET @safe_collation_server= @@collation_server;
SET @safe_character_set_client= @@character_set_client;
SET @safe_character_set_results= @@character_set_results;
SET character_set_server= @test_character_set;
SET collation_server= @test_collation;
CREATE DATABASE d1;
USE d1;
CREATE TABLE t1 (c CHAR(10), KEY(c));
SHOW FULL COLUMNS FROM t1;
Field Type Collation Null Key Default Extra Privileges Comment
c char(10) cp1250_general_ci YES MUL NULL
INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa');
SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%';
want3results
aaa
aaaa
aaaaa
DROP TABLE t1;
CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2)));
SHOW FULL COLUMNS FROM t1;
Field Type Collation Null Key Default Extra Privileges Comment
c1 varchar(15) cp1250_general_ci YES MUL NULL
INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab');
SELECT c1 as want3results from t1 where c1 like 'l%';
want3results
location
loberge
lotre
SELECT c1 as want3results from t1 where c1 like 'lo%';
want3results
location
loberge
lotre
SELECT c1 as want1result from t1 where c1 like 'loc%';
want1result
location
SELECT c1 as want1result from t1 where c1 like 'loca%';
want1result
location
SELECT c1 as want1result from t1 where c1 like 'locat%';
want1result
location
SELECT c1 as want1result from t1 where c1 like 'locati%';
want1result
location
SELECT c1 as want1result from t1 where c1 like 'locatio%';
want1result
location
SELECT c1 as want1result from t1 where c1 like 'location%';
want1result
location
DROP TABLE t1;
create table t1 (a set('a') not null);
insert into t1 values (),();
Warnings:
Warning 1364 Field 'a' doesn't have a default value
select cast(a as char(1)) from t1;
cast(a as char(1))
select a sounds like a from t1;
a sounds like a
1
1
select 1 from t1 order by cast(a as char(1));
1
1
1
drop table t1;
set names utf8;
create table t1 (
name varchar(10),
level smallint unsigned);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`name` varchar(10) DEFAULT NULL,
`level` smallint(5) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1250
insert into t1 values ('string',1);
select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
concat(name,space(level)) concat(name, repeat(' ',level))
string string
drop table t1;
DROP DATABASE d1;
USE test;
SET character_set_server= @safe_character_set_server;
SET collation_server= @safe_collation_server;
SET character_set_client= @safe_character_set_client;
SET character_set_results= @safe_character_set_results;
SET @test_character_set= 'cp1250';
SET @test_collation= 'cp1250_czech_cs';
SET @safe_character_set_server= @@character_set_server;
SET @safe_collation_server= @@collation_server;
SET @safe_character_set_client= @@character_set_client;
SET @safe_character_set_results= @@character_set_results;
SET character_set_server= @test_character_set;
SET collation_server= @test_collation;
CREATE DATABASE d1;
USE d1;
CREATE TABLE t1 (c CHAR(10), KEY(c));
SHOW FULL COLUMNS FROM t1;
Field Type Collation Null Key Default Extra Privileges Comment
c char(10) cp1250_czech_cs YES MUL NULL
INSERT INTO t1 VALUES ('aaa'),('aaaa'),('aaaaa');
SELECT c as want3results FROM t1 WHERE c LIKE 'aaa%';
want3results
aaa
aaaa
aaaaa
DROP TABLE t1;
CREATE TABLE t1 (c1 varchar(15), KEY c1 (c1(2)));
SHOW FULL COLUMNS FROM t1;
Field Type Collation Null Key Default Extra Privileges Comment
c1 varchar(15) cp1250_czech_cs YES MUL NULL
INSERT INTO t1 VALUES ('location'),('loberge'),('lotre'),('boabab');
SELECT c1 as want3results from t1 where c1 like 'l%';
want3results
location
loberge
lotre
SELECT c1 as want3results from t1 where c1 like 'lo%';
want3results
location
loberge
lotre
SELECT c1 as want1result from t1 where c1 like 'loc%';
want1result
location
SELECT c1 as want1result from t1 where c1 like 'loca%';
want1result
location
SELECT c1 as want1result from t1 where c1 like 'locat%';
want1result
location
SELECT c1 as want1result from t1 where c1 like 'locati%';
want1result
location
SELECT c1 as want1result from t1 where c1 like 'locatio%';
want1result
location
SELECT c1 as want1result from t1 where c1 like 'location%';
want1result
location
DROP TABLE t1;
create table t1 (a set('a') not null);
insert into t1 values (),();
Warnings:
Warning 1364 Field 'a' doesn't have a default value
select cast(a as char(1)) from t1;
cast(a as char(1))
select a sounds like a from t1;
a sounds like a
1
1
select 1 from t1 order by cast(a as char(1));
1
1
1
drop table t1;
set names utf8;
create table t1 (
name varchar(10),
level smallint unsigned);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`name` varchar(10) COLLATE cp1250_czech_cs DEFAULT NULL,
`level` smallint(5) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_cs
insert into t1 values ('string',1);
select concat(name,space(level)), concat(name, repeat(' ',level)) from t1;
concat(name,space(level)) concat(name, repeat(' ',level))
string string
drop table t1;
DROP DATABASE d1;
USE test;
SET character_set_server= @safe_character_set_server;
SET collation_server= @safe_collation_server;
SET character_set_client= @safe_character_set_client;
SET character_set_results= @safe_character_set_results;
CREATE TABLE t1 (a char(16)) character set cp1250 collate cp1250_czech_cs;
INSERT INTO t1 VALUES ('');
SELECT a, length(a), a='', a=' ', a=' ' FROM t1;
a length(a) a='' a=' ' a=' '
0 1 1 1
DROP TABLE t1;
CREATE TABLE t1 (
popisek varchar(30) collate cp1250_general_ci NOT NULL default '',
PRIMARY KEY (`popisek`)
);
INSERT INTO t1 VALUES ('2005-01-1');
SELECT * FROM t1 WHERE popisek = '2005-01-1';
popisek
2005-01-1
SELECT * FROM t1 WHERE popisek LIKE '2005-01-1';
popisek
2005-01-1
drop table t1;
set names cp1250;
CREATE TABLE t1
(
id INT AUTO_INCREMENT PRIMARY KEY,
str VARCHAR(32) CHARACTER SET cp1250 COLLATE cp1250_czech_cs NOT NULL default '',
UNIQUE KEY (str)
);
INSERT INTO t1 VALUES (NULL, 'a');
INSERT INTO t1 VALUES (NULL, 'aa');
INSERT INTO t1 VALUES (NULL, 'aaa');
INSERT INTO t1 VALUES (NULL, 'aaaa');
INSERT INTO t1 VALUES (NULL, 'aaaaa');
INSERT INTO t1 VALUES (NULL, 'aaaaaa');
INSERT INTO t1 VALUES (NULL, 'aaaaaaa');
select * from t1 where str like 'aa%';
id str
2 aa
3 aaa
4 aaaa
5 aaaaa
6 aaaaaa
7 aaaaaaa
drop table t1;
set names cp1250;
create table t1 (a varchar(15) collate cp1250_czech_cs NOT NULL, primary key(a));
insert into t1 values("abcdefghá");
insert into t1 values("ááèè");
select a from t1 where a like "abcdefghá";
a
abcdefghá
drop table t1;
|