详解mysql数据表的分表策略

<?php
function get_hash_table($table,$userid) {
    $str = crc32($userid);
    if($str<0){
        $hash = '0'.substr(abs($str), 0, 1);
    }else{
        $hash = substr($str, 0, 2);
    }
    return $table.'_'.$hash;
}
echo get_hash_table('message','user18991');     //结果为message_10
echo get_hash_table('message','user34523');    //结果为message_13
 
?>

CREATE TABLE `test`.`user` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`pwd` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`email` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
) ENGINE = myisam DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
CREATE TABLE `test`.`user1` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`pwd` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`email` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
) ENGINE = MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
  
CREATE TABLE `test`.`user2` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`pwd` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`email` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
) ENGINE = MYISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
 
INSERT INTO `test`.`user` (`id`, `username`, `pwd`, `email`) VALUES (NULL, 'user1', '123', 'user1@jb200.com');
INSERT INTO `test`.`user` (`id`, `username`, `pwd`, `email`) VALUES (NULL, 'user2', '123', 'user2@jb200.com');
INSERT INTO `test`.`user` (`id`, `username`, `pwd`, `email`) VALUES (NULL, 'user3', '123', 'user3@jb200.com');
INSERT INTO `test`.`user` (`id`, `username`, `pwd`, `email`) VALUES (NULL, 'user4', '123', 'user4@jb200.com');
 
INSERT INTO user1(user1.id,user1.username,user1.pwd,user1.email) SELECT user.id,user.username,user.pwd,user.email FROM user where user.id >=2;
INSERT INTO user2(user2.id,user2.username,user2.pwd,user2.email) SELECT user.id,user.username,user.pwd,user.email FROM user where user.id >2;
 
DROP TABLE `user`;
CREATE TABLE `test`.`user` (
`id` INT( 11 ) NOT NULL ,
`username` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`pwd` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`email` VARCHAR( 300 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
INDEX ( `id` )
) ENGINE = MRG_MYISAM UNION=(user1,user2) INSERT_METHOD=LAST CHARSET=utf8  AUTO_INCREMENT=1 ;
 

INSERT INTO `user` (`id`,`username`, `pwd`,`email`) VALUES(5,'user5','123','user5@jb200.com');
INSERT INTO `user` (`id`,`username`, `pwd`,`email`) VALUES(6,'user6','123','user6@jb200.com');
INSERT INTO `user` (`id`,`username`, `pwd`,`email`) VALUES(7,'user7','123','user7@jb200.com');
 
INSERT INTO `user` (`username`, `pwd`,`email`) VALUES('user8','123','user8@jb200.com'); 
//这样的话 id居然是0  这个有点奇怪，如果解决不了，每次插入新数据，要加上last_id。（php代码得改）