1
'#--------------------FN_DYNVARS_097_01-------------------------#'
2
SET @@global.myisam_stats_method = nulls_equal;
3
'connect (con1,localhost,root,,,,)'
5
SELECT @@global.myisam_stats_method;
6
@@global.myisam_stats_method
8
SELECT @@session.myisam_stats_method;
9
@@session.myisam_stats_method
11
'#--------------------FN_DYNVARS_097_02-------------------------#'
13
DROP TABLE IF EXISTS t1;
14
CREATE TABLE t1 (a int, key (a));
15
INSERT INTO t1 VALUES (0),(1),(2),(3),(4);
16
INSERT INTO t1 SELECT NULL FROM t1;
17
'default: NULLs considered unequal'
18
SET myisam_stats_method=nulls_unequal;
20
Table Op Msg_type Msg_text
21
test.t1 analyze status OK
23
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
24
t1 1 a 1 a A 10 NULL NULL YES BTREE
25
INSERT INTO t1 VALUES (11);
26
DELETE FROM t1 WHERE a=11;
28
Table Op Msg_type Msg_text
29
test.t1 check status OK
31
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
32
t1 1 a 1 a A 10 NULL NULL YES BTREE
33
'Set nulls to be equal'
34
SET myisam_stats_method=nulls_equal;
35
INSERT INTO t1 VALUES (11);
36
DELETE FROM t1 WHERE a=11;
38
Table Op Msg_type Msg_text
39
test.t1 analyze status OK
41
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
42
t1 1 a 1 a A 5 NULL NULL YES BTREE
43
INSERT INTO t1 VALUES (11);
44
DELETE FROM t1 WHERE a=11;
46
Table Op Msg_type Msg_text
47
test.t1 check status OK
49
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
50
t1 1 a 1 a A 5 NULL NULL YES BTREE
51
'Set nulls to be ignored'
52
SET myisam_stats_method=nulls_ignored;
53
SHOW variables LIKE 'myisam_stats_method';
55
myisam_stats_method nulls_ignored
58
a char(3), b char(4), c char(5), d char(6),
61
INSERT INTO t1 VALUES ('bcd','def1', NULL, 'zz');
62
INSERT INTO t1 VALUES ('bcd','def2', NULL, 'zz');
63
INSERT INTO t1 VALUES ('bce','def1', 'yuu', NULL);
64
INSERT INTO t1 VALUES ('bce','def2', NULL, 'quux');
66
Table Op Msg_type Msg_text
67
test.t1 analyze status OK
69
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
70
t1 1 a 1 a A 2 NULL NULL YES BTREE
71
t1 1 a 2 b A 4 NULL NULL YES BTREE
72
t1 1 a 3 c A 4 NULL NULL YES BTREE
73
t1 1 a 4 d A 4 NULL NULL YES BTREE
76
Table Op Msg_type Msg_text
77
test.t1 analyze status OK
79
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
80
t1 1 a 1 a A 0 NULL NULL YES BTREE
81
t1 1 a 2 b A 0 NULL NULL YES BTREE
82
t1 1 a 3 c A 0 NULL NULL YES BTREE
83
t1 1 a 4 d A 0 NULL NULL YES BTREE
84
SET myisam_stats_method=DEFAULT;