create table mytbl(
id int primary key,
name varchar(50)
)type=MyISAM;
alter table table_name type = InnoDB;
show table status from table_name;
show create table table_name
//这个就是select锁表的一种,不明确主键。增删改查都可能会导致锁全表,在以后我们会详细列出。
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
create table testMyIsam(
id int unsigned primary key auto_increment,
name varchar(20) not null
)engine=myisam;
create table testInnoDB(
id int unsigned primary key auto_increment,
name varchar(20) not null
)engine=innodb;
//创建存储过程
delimiter $$
drop procedure if exists ptestmyisam;
create procedure ptestmyisam()
begin
declare pid int ;
set pid = 1000000;
while pid>0
do
insert into testmyisam(name) values(concat("fuzhu", pid));
set pid = pid-1;
end while;
end $$
//使用存储过程:
call ptestmyisam();
//创建存储过程(尽量把Innodb的数量级压低,不然,,卡在那里半天也不奇怪)
delimiter $$
drop procedure if exists ptestInndb;
create procedure ptestInndb()
begin
declare pid int ;
set pid = 1000000;
while pid>0
do
insert into testinnodb(name) values(concat("fuzhu", pid));
set pid = pid-1;
end while;
end $$
//使用存储过程:
call ptestInndb();
//停掉事务
set autocommit = 0;
//调用存储过程
call ptestInndb;
//重启事务
set autocommit = 1;
//耗时3秒多
update testinnodb set name = 'fuzhu' where id>0 and id<10000;
//耗时0.171秒
update testmyisam set name = 'fuzhu' where id>0 and id<13525;
select count(*) from testInnoDB;
select count(*) from testMyIsam;
select * from testMyIsam where name > "fuzhu100" ;
select * from testInnoDB where name > "fuzhu100" ;
select * from testMyIsam where id > 10 ;
select * from testinnodb where id > 10 ;
//用于把表的拷贝从主服务器转移到从属服务器。
LOAD TABLE tbl_name FROM MASTER
CREATE TABLE tbHeap (
id int unsigned primary key auto_increment,
name varchar(20) not null
) TYPE=Heap
//用户表一
CREATE TABLE IF NOT EXISTS `user1` (
`id` int(11) NOT NULL ,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
//用户表二
CREATE TABLE IF NOT EXISTS `user2` (
`id` int(11) NOT NULL ,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
//分别插入两条测试数据先
INSERT INTO `user1` (`name`) VALUES('辅助');
INSERT INTO `user2` (`name`) VALUES('JackFrost');
CREATE TABLE IF NOT EXISTS `alluser` (
`id` int(11) NOT NULL ,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MRG_MYISAM
DEFAULT CHARSET=utf8
UNION=(user1,user2) ;
select id,name from alluser;
//就是插入总表的时候,其实也是插入到第一个分表。
ALTER TABLE `test_engine`.`alluser` INSERT_METHOD = FIRST;
INSERT INTO `alluser` (id,`name`) VALUES(2,'插入到第一个分表');
//就是插入总表的时候,其实也是插入到最后一个分表。
ALTER TABLE `test_engine`.`alluser` INSERT_METHOD = LAST;
CREATE TABLE `create_id` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;
insert into create_id () values();
INSERT INTO {$table_name} (id, name) VALUES ('{$id}');