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
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
|
create table t1 (f1 int not null, f2 int not null, f3 int not null, f4 int not null, primary key(f1,f2,f3,f4)) engine=myisam;
insert into t1 values (1,1,1,0),(1,1,2,0),(1,1,3,0),(1,2,1,0),(1,2,2,0),(1,2,3,0),(1,3,1,0),(1,3,2,0),(1,3,3,0),(1,1,1,1),(1,1,2,1),(1,1,3,1),(1,2,1,1),(1,2,2,1),(1,2,3,1),(1,3,1,1),(1,3,2,1),(1,3,3,1);
-- Here we enable metadata just to check that the collation of the
-- resultset is non-binary for string type. This should be changed
-- after Bug#29394 is implemented.
analyze table t1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def Table 253 512 7 Y 0 31 255
def Op 253 40 7 Y 0 31 255
def Msg_type 253 40 6 Y 0 31 255
def Msg_text 250 1572864 2 Y 0 31 255
Table Op Msg_type Msg_text
test.t1 analyze status OK
show index from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
t1 0 PRIMARY 1 f1 A 1 NULL NULL BTREE YES NULL
t1 0 PRIMARY 2 f2 A 3 NULL NULL BTREE YES NULL
t1 0 PRIMARY 3 f3 A 9 NULL NULL BTREE YES NULL
t1 0 PRIMARY 4 f4 A 18 NULL NULL BTREE YES NULL
-- Here we enable metadata just to check that the collation of the
-- resultset is non-binary for string type. This should be changed
-- after Bug#29394 is implemented.
repair table t1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def Table 253 512 7 Y 0 31 255
def Op 253 40 6 Y 0 31 255
def Msg_type 253 40 6 Y 0 31 255
def Msg_text 250 1572864 2 Y 0 31 255
Table Op Msg_type Msg_text
test.t1 repair status OK
show index from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
t1 0 PRIMARY 1 f1 A 1 NULL NULL BTREE YES NULL
t1 0 PRIMARY 2 f2 A 3 NULL NULL BTREE YES NULL
t1 0 PRIMARY 3 f3 A 9 NULL NULL BTREE YES NULL
t1 0 PRIMARY 4 f4 A 18 NULL NULL BTREE YES NULL
drop table t1;
create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int NOT NULL,
`b` varchar(10) DEFAULT NULL,
KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test'
alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int NOT NULL,
`b` varchar(10) DEFAULT NULL,
KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test'
ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int NOT NULL,
`b` varchar(10) DEFAULT NULL,
KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
drop table t1;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
create table t1 (
type_bool bool not null default 0,
type_tiny tinyint not null auto_increment primary key,
type_short smallint(3),
type_mediumint mediumint,
type_bigint bigint,
type_decimal decimal(5,2),
type_numeric numeric(5,2),
empty_char char(0),
type_char char(2),
type_varchar varchar(10),
type_timestamp timestamp not null default current_timestamp on update current_timestamp,
type_date date not null default '0000-00-00',
type_time time not null default '00:00:00',
type_datetime datetime not null default '0000-00-00 00:00:00',
type_year year,
type_enum enum ('red', 'green', 'blue'),
type_set enum ('red', 'green', 'blue'),
type_tinyblob tinyblob,
type_blob blob,
type_medium_blob mediumblob,
type_long_blob longblob,
index(type_short)
) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed CHARSET=latin1;
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`type_bool` tinyint(1) NOT NULL DEFAULT '0',
`type_tiny` tinyint NOT NULL AUTO_INCREMENT,
`type_short` smallint DEFAULT NULL,
`type_mediumint` mediumint DEFAULT NULL,
`type_bigint` bigint DEFAULT NULL,
`type_decimal` decimal(5,2) DEFAULT NULL,
`type_numeric` decimal(5,2) DEFAULT NULL,
`empty_char` char(0) DEFAULT NULL,
`type_char` char(2) DEFAULT NULL,
`type_varchar` varchar(10) DEFAULT NULL,
`type_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`type_date` date NOT NULL DEFAULT '0000-00-00',
`type_time` time NOT NULL DEFAULT '00:00:00',
`type_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`type_year` year DEFAULT NULL,
`type_enum` enum('red','green','blue') DEFAULT NULL,
`type_set` enum('red','green','blue') DEFAULT NULL,
`type_tinyblob` tinyblob,
`type_blob` blob,
`type_medium_blob` mediumblob,
`type_long_blob` longblob,
PRIMARY KEY (`type_tiny`),
KEY `type_short` (`type_short`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test'
insert into t1 (type_timestamp) values ("2003-02-07 10:00:01");
select * from t1;
type_bool type_tiny type_short type_mediumint type_bigint type_decimal type_numeric empty_char type_char type_varchar type_timestamp type_date type_time type_datetime type_year type_enum type_set type_tinyblob type_blob type_medium_blob type_long_blob
0 1 NULL NULL NULL NULL NULL NULL NULL NULL 2003-02-07 10:00:01 0000-00-00 00:00:00 0000-00-00 00:00:00 NULL NULL NULL NULL NULL NULL NULL
drop table t1;
SET sql_mode = default;
CREATE TABLE t1 (i int, KEY (i)) ENGINE=MEMORY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL,
KEY `i` (`i`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY USING HASH (i)) ENGINE=MEMORY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL,
KEY `i` (`i`) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MEMORY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL,
KEY `i` (`i`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL,
KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL,
KEY `i` (`i`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL,
KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 ENGINE=MEMORY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL,
KEY `i` (`i`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL,
KEY `i` (`i`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 ENGINE=MEMORY;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`i` int DEFAULT NULL,
KEY `i` (`i`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
flush status;
show variables like "log_queries_not_using_indexes";
Variable_name Value
log_queries_not_using_indexes ON
CREATE TABLE `tab1` (
`c1` int(11) default NULL,
`c2` char(20) default NULL,
`c3` char(20) default NULL,
KEY `k1` (`c2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
INSERT INTO tab1 values(1, "hello", "world");
INSERT INTO tab1 values(2, "hello2", "world2");
INSERT INTO tab1 values(3, "hello3", "world3");
select SQL_NO_CACHE * from tab1;
c1 c2 c3
1 hello world
2 hello2 world2
3 hello3 world3
Warnings:
Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release.
show status like 'slow_queries';
Variable_name Value
Slow_queries 1
set global log_queries_not_using_indexes=OFF;
show variables like "log_queries_not_using_indexes";
Variable_name Value
log_queries_not_using_indexes OFF
select SQL_NO_CACHE * from tab1;
c1 c2 c3
1 hello world
2 hello2 world2
3 hello3 world3
Warnings:
Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release.
show status like 'slow_queries';
Variable_name Value
Slow_queries 1
set global log_queries_not_using_indexes=ON;
show variables like "log_queries_not_using_indexes";
Variable_name Value
log_queries_not_using_indexes ON
select SQL_NO_CACHE * from tab1;
c1 c2 c3
1 hello world
2 hello2 world2
3 hello3 world3
Warnings:
Warning 1681 'SQL_NO_CACHE' is deprecated and will be removed in a future release.
show status like 'slow_queries';
Variable_name Value
Slow_queries 2
drop table tab1;
show variables like 'myisam_recover_options';
Variable_name Value
myisam_recover_options OFF
CREATE TABLE t1 (
Codigo int(10) unsigned NOT NULL auto_increment,
Nombre varchar(255) default NULL,
Telefono varchar(255) default NULL,
Observaciones longtext,
Direccion varchar(255) default NULL,
Dni varchar(255) default NULL,
CP int(11) default NULL,
Provincia varchar(255) default NULL,
Poblacion varchar(255) default NULL,
PRIMARY KEY (Codigo)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
show create table t1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def Table 253 256 2 N 1 31 255
def Create Table 253 4096 440 N 1 31 255
Table Create Table
t1 CREATE TABLE `t1` (
`Codigo` int unsigned NOT NULL AUTO_INCREMENT,
`Nombre` varchar(255) DEFAULT NULL,
`Telefono` varchar(255) DEFAULT NULL,
`Observaciones` longtext,
`Direccion` varchar(255) DEFAULT NULL,
`Dni` varchar(255) DEFAULT NULL,
`CP` int DEFAULT NULL,
`Provincia` varchar(255) DEFAULT NULL,
`Poblacion` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Codigo`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3
drop table t1;
|
