partition_02myisam.result

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) PARTITIONS 1 
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 1
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) SUBPARTITIONS 1 (PARTITION part1 VALUES LESS THAN (100) , PARTITION part2 VALUES LESS THAN (2147483647) )
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
INSERT INTO t1 SELECT * FROM t0_template WHERE f1 BETWEEN 1 AND 200;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
UPDATE t1 SET f1 = f1 + 200
WHERE f1 BETWEEN 100 - 50 AND 100 + 50;
SELECT (COUNT(*) = 200) AND (MIN(f1) = 1) AND (MAX(f1) = 100 + 50 + 200 )
AS my_value FROM t1;
my_value
1
DELETE FROM t1
WHERE f1 BETWEEN 100 - 50 + 200 AND 100 + 50 + 200;
SELECT (COUNT(*) = 200 - 50 - 50 - 1) AND (MIN(f1) = 1) AND (MAX(f1) = 200)
AS my_value FROM t1;
my_value
1
INSERT INTO t1 SET f1 = 0 , f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 0 AND f2 = '#######';
my_value
1
INSERT INTO t1 SET f1 = 200 + 1, f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 1 AND f2 = '#######';
my_value
1
UPDATE t1 SET f1 = 200 + 2, f2 = 'ZZZZZZZ'
     WHERE  f1 = 0 AND f2 = '#######';
SELECT COUNT(*) = 1 AS my_value FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
my_value
1
DELETE FROM t1 WHERE f1 = 200 + 2 AND f2 = 'ZZZZZZZ';
SELECT COUNT(*) = 0 AS my_value FROM t1 WHERE f2 = 'ZZZZZZZ';
my_value
1
TRUNCATE t1;
SELECT COUNT(*) = 0 AS my_value FROM t1;
my_value
1
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 0;
ERROR HY000: Number of partitions = 0 is not an allowed value
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 0
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR HY000: Number of subpartitions = 0 is not an allowed value
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS -1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS -1
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 1000000;
ERROR HY000: Too many partitions were defined
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 1000000
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR HY000: Too many partitions were defined
# 3.2.4 partition/subpartition numbers STRING notation
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS '2';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS '2'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS '2.0';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS '2.0'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2.0'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN ' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS '0.2E+1';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS '0.2E+1'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0.2E+1'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS TH' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS '2A';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS '2A'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2A'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 'A2';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 'A2'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''A2'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS '';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS ''
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '''
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 'GARBAGE';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 'GARBAGE'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''GARBAGE'
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS T' at line 3
# 3.2.5 partition/subpartition numbers other notations
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 2A;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS 2A
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2A
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS A2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS A2
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A2
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (21' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS GARBAGE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS GARBAGE
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GARBAGE
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THA' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS "2";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS "2"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS "2A";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS "2A"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"2A"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS "A2";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS "A2"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"A2"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (' at line 3
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS "GARBAGE";
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION    BY RANGE(f1) SUBPARTITION BY HASH(f1)
SUBPARTITIONS "GARBAGE"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS THAN (2147483647));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"GARBAGE"
(PARTITION part1 VALUES LESS THAN (100), PARTITION part2 VALUES LESS T' at line 3
# 3.3 Mixups of assigned partition/subpartition numbers and names
# 3.3.1 (positive) number of partition/subpartition
#                         = number of named partition/subpartition
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1, PARTITION part2 ) ;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (f1) (PARTITION part1  ENGINE = MyISAM, PARTITION part2  ENGINE = MyISAM)
DROP TABLE t1;
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1) PARTITIONS 2
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21, SUBPARTITION subpart22)
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL,
  `f2` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f1) SUBPARTITION BY HASH (f1) (PARTITION part1 VALUES LESS THAN (1000)  (SUBPARTITION subpart11 ENGINE = MyISAM, SUBPARTITION subpart12 ENGINE = MyISAM), PARTITION part2 VALUES LESS THAN (2147483647)  (SUBPARTITION subpart21 ENGINE = MyISAM, SUBPARTITION subpart22 ENGINE = MyISAM))
DROP TABLE t1;
# 3.3.2 (positive) number of partition/subpartition ,
#                  0 (= no) named partition/subpartition
#                  already checked above
# 3.3.3 (negative) number of partitions/subpartitions
#                         > number of named partitions/subpartitions
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY HASH(f1) PARTITIONS 2 ( PARTITION part1 ) ;
ERROR 42000: Wrong number of partitions defined, mismatch with previous setting near ')' at line 2
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11 ),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21, SUBPARTITION subpart22)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21, SUBPAR' at line 5
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1)
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2000)
(SUBPARTITION subpart21 ),
PARTITION part3 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart31, SUBPARTITION subpart32)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '),
PARTITION part3 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart31, SUBPAR' at line 7
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY RANGE(f1) PARTITIONS 2
SUBPARTITION BY HASH(f1) SUBPARTITIONS 2
( PARTITION part1 VALUES LESS THAN (1000)
(SUBPARTITION subpart11, SUBPARTITION subpart12),
PARTITION part2 VALUES LESS THAN (2147483647)
(SUBPARTITION subpart21                        )
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near ')