ndb_insert.test

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

(1981,1981,1981),(1982,1982,1982),(1983,1983,1983),(1984,1984,1984),(1985,1985,1985),
(1986,1986,1986),(1987,1987,1987),(1988,1988,1988),(1989,1989,1989),(1990,1990,1990),
(1991,1991,1991),(1992,1992,1992),(1993,1993,1993),(1994,1994,1994),(1995,1995,1995),
(1996,1996,1996),(1997,1997,1997),(1998,1998,1998),(1999,1999,1999);

SELECT COUNT(*) FROM t1;


#
# Insert duplicate rows
# 
--error 1062
INSERT INTO t1 VALUES (1,1,1);

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

select count(*) from t1;


#
# Test that select count(*) can see inserts made in the same transaction
#
begin;
SELECT COUNT(*) FROM t1;
INSERT INTO t1 VALUES 
(2001,2001,2001),(2002,2002,2002),(2003,2003,2003),(2004,2004,2004),(2005,2005,2005);
SELECT COUNT(*) FROM t1;
rollback;

#
# Insert duplicate rows, inside transaction
# try to commit 
#
begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

--error 1296
commit;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;


#
# Insert duplicate rows, inside transaction
# rollback 
#
begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

rollback;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;


#
# Insert duplicate rows, inside transaction
# then try to select, finally rollback
#
begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);
--error 1296
SELECT * FROM t1 WHERE pk1=10;

rollback;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;


#
# Insert duplicate rows, inside transaction
# then try to select, finally commit
#
begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

--error 1296
SELECT * FROM t1 WHERE pk1=10;

--error 1296
SELECT * FROM t1 WHERE pk1=10;

--error 1296
commit;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;


#
# Insert duplicate rows, inside transaction
# then try to do another insert
#
begin;

--error 1022
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);

--error 1296
INSERT INTO t1 values (4000, 40, 44);

rollback;

select * from t1 where pk1=1;
select * from t1 where pk1=10;
select count(*) from t1 where pk1 <= 10 order by pk1;
select count(*) from t1;

#
# Insert duplicate rows using "insert .. select" 

#
--error 1022
insert into t1 select * from t1 where b < 10 order by pk1;

DELETE FROM t1 WHERE pk1=2;

begin;
INSERT IGNORE INTO t1 VALUES(1,2,3),(2,3,4);
select * from t1 where pk1 < 3 order by pk1;
rollback;

INSERT IGNORE INTO t1 VALUES(1,2,3),(2,3,4);
select * from t1 where pk1 < 3 order by pk1;

REPLACE INTO t1 values(1, 78, 3);
select * from t1 where pk1=1;

INSERT INTO t1 VALUES(1,1,1),(3,4,5) ON DUPLICATE KEY UPDATE b=79;
select * from t1 where pk1 < 4 order by pk1;

INSERT INTO t1 VALUES(1,1,1),(3,4,5) ON DUPLICATE KEY UPDATE b=pk1+c;
select * from t1 where pk1 < 4 order by pk1;

DELETE FROM t1 WHERE pk1 = 2 OR pk1 = 4 OR pk1 = 6;
INSERT INTO t1 VALUES(1,1,1),(2,2,17),(3,4,5) ON DUPLICATE KEY UPDATE pk1=b;
select * from t1 where pk1 = b and b != c order by pk1;

# Test handling of duplicate unique
DELETE FROM t1;
CREATE UNIQUE INDEX bi ON t1(b);
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10);
INSERT INTO t1 VALUES(0,1,0),(21,21,21) ON DUPLICATE KEY UPDATE pk1=b+10,b=b+10;
select * from t1 order by pk1;

DROP TABLE t1;

#
# Bug #6331: problem with 'insert ignore'
#

CREATE TABLE t1(a INT) ENGINE=ndb;
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 SELECT a FROM t1;
INSERT IGNORE INTO t1 SELECT a FROM t1;
INSERT IGNORE INTO t1 SELECT a FROM t1;
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 VALUES (1);
SELECT * FROM t1 ORDER BY a;
DELETE FROM t1;
CREATE UNIQUE INDEX ai ON t1(a);
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 VALUES (1);
INSERT IGNORE INTO t1 VALUES (NULL),(2);
SELECT * FROM t1 ORDER BY a;
DROP TABLE t1;

# Ignore and NULL values
CREATE TABLE t1(pk INT NOT NULL PRIMARY KEY, a INT, UNIQUE (a)) ENGINE=ndb;
INSERT IGNORE INTO t1 VALUES (1,1),(2,2),(3,3);
INSERT IGNORE INTO t1 VALUES (4,NULL),(5,NULL),(6,NULL),(7,4);
SELECT * FROM t1 ORDER BY pk;
DROP TABLE t1;

# End of 4.1 tests