MySQL动态创建表,数据分表的存储过程

复制代码 代码如下:

BEGIN
DECLARE `@i` int(11);
DECLARE `@siteCount` int(11);
DECLARE `@sqlstr` VARCHAR(2560);
DECLARE `@sqlinsert` VARCHAR(2560); //以上声明变量
SELECT COUNT(0) into `@siteCount` FROM tbl_base_site; //计算表tbl_base_site的记录总条数
set `@i`=1;
WHILE (`@i`-1)*300<`@siteCount` DO //while循环执行
SET @sqlstr = CONCAT('CREATE TABLE tbl_base_syslog',`@i`,'(syslog_id INT(11) AUTO_INCREMENT PRIMARY KEY,
create_user VARCHAR(32),
description text,
create_time datetime,
site_id INT(11),
ip VARCHAR(64),
version_id SMALLINT(2),
module_identity VARCHAR(64),
right_name VARCHAR(64)
)');
prepare stmt from @sqlstr;
execute stmt;
//以上实现动态创建表
SET @sqlinsert = CONCAT('INSERT INTO tbl_base_syslog',`@i`,'(
syslog_id,
create_user,
description,
create_time,
site_id,
ip,
version_id,
module_identity,
right_name)
SELECT syslog_id,
create_user,
description,
create_time,
site_id,
ip,
version_id,
module_identity,
right_name
FROM tbl_base_syslog
WHERE site_id IN (select tbs.site_id from (select site_id from tbl_base_site limit ',`@i`*300,',300) as tbs)
ORDER BY syslog_id');
prepare stmt from @sqlinsert;
execute stmt;
//以上实现从一张表查询记录插入到动态创建的新表中
SET `@i`= `@i`+1;

END WHILE;
END

时间: 2011-08-09

MySQL 建表的优化策略 小结

目录 1. 字符集的选择 1 2. 主键 1 3. 外键 2 4. 索引 2 4.1. 以下情况适合于创建索引 2 4.2. 以下的情况下不适合创建索引 3 4.3. 联合索引 3 4.4. 索引长度 4 5. 特殊字段 4 5.1. 冗余字段 4 5.2. 分割字段 4 5.3. BLOB和CLOB 5 6. 特殊 5 6.1. 表格分割 5 6.2. 使用非事务表类型 5 1. 字符集的选择 如果确认全部是中文,不会使用多语言以及中文无法表示的字符,那么GBK是首选. 采用UTF-8编码会占

mysql建表常用sql语句个人经验分享

连接:mysql -h主机地址 -u用户名 -p用户密码 (注:u与root可以不用加空格,其它也一样) 断开:exit (回车) 创建授权:grant select on 数据库.* to 用户名@登录主机 identified by \"密码\" 修改密码:mysqladmin -u用户名 -p旧密码 password 新密码 删除授权: revoke select,insert,update,delete om *.* from test2@localhost; 显示数据库:sh

MySQL SQL语句优化的10条建议

1.将经常要用到的字段(比如经常要用这些字段来排序,或者用来做搜索),则最好将这些字段设为索引.2.字段的种类尽可能用int 或者tinyint类型.另外字段尽可能用NOT NULL.3.当然无可避免某些字段会用到text ,varchar等字符类型,最好将text字段的单独出另外一个表出来(用主键关联好)4.字段的类型,以及长度,是一个很考究开发者优化功力的一个方面.如果表数据有一定的量了,不妨用PROCEDURE ANALYSE()命令来取得字段的优化建议!(在phpmyadmin里可以在查

MySQL查看、创建和删除索引的方法

本文实例讲述了MySQL查看.创建和删除索引的方法.分享给大家供大家参考.具体如下: 1.索引作用 在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率.特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍. 例如,有3个未索引的表t1.t2.t3,分别只包含列c1.c2.c3,每个表分别含有1000行数据组成,指为1-1000的数值,查找对应值相等行的查询如下所示. SELECT c1,c2,c3 FROM t1,t2,t3

MySQL 索引分析和优化

一.什么是索引? 索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存.如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录.表里面的记录数量越多,这个操作的代价就越高.如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置.如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍. 假设我们创建了一个名为people的表: CREATE TABLE people ( p

mysql 添加索引 mysql 如何创建索引

1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEX

MySQL索引类型总结和使用技巧以及注意事项

在数据库表中,对字段建立索引可以大大提高查询速度.假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin. 在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在

Mysql 建库建表技巧分享

一.两表之间若有关联,你是否还在用主键进行关联? 比如现在有2张表,一张新闻栏目表,一张新闻表,现在两张表需要进行关联,我想大多数人的做法肯定是在新闻表里建一个新闻栏目id,然后把新闻栏目表里的主键ID(自增)写到这个字段里,通过这样进行两表关联. 如果你是这样做的,赶紧改掉这个习惯吧.也许你会问为什么,栏目id是主键啊,又是自增的,为什么这样操作不行?原因其实很简单,栏目我们会增加,也会删除,删除就会造成主键id之间会有断号的情况,由于主键设置为自增,也就是说你之前删掉的栏目,再进行添加,id

mysql 表空间及索引的查看方法

1.查看索引 (1)单位是GB SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'database'; +------------------+ | Total Index Size | +------------------+ | 1.70 GB | +--

MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法总结

本文实例讲述了MySQL常用的建表.添加字段.修改字段.添加索引SQL语句写法.分享给大家供大家参考,具体如下: 建表: DROP TABLE IF EXISTS bulletin; CREATE TABLE bulletin( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, # 主键 uid INT(11) NOT NULL DEFAULT 0, # 创建者id context VARCHAR(600) NOT NULL DEFAULT '', # 公告

Mysql建表与索引使用规范详解

一. MySQL建表,字段需设置为非空,需设置字段默认值.二. MySQL建表,字段需NULL时,需设置字段默认值,默认值不为NULL.三. MySQL建表,如果字段等价于外键,应在该字段加索引.四. MySQL建表,不同表之间的相同属性值的字段,列类型,类型长度,是否非空,是否默认值,需保持一致,否则无法正确使用索引进行关联对比.五. MySQL使用时,一条SQL语句只能使用一个表的一个索引.所有的字段类型都可以索引,多列索引的属性最多15个.六. 如果可以在多个索引中进行选择,MySQL通常

MYSQL索引无效和索引有效的详细介绍

1.WHERE字句的查询条件里有不等于号(WHERE column!=...),MYSQL将无法使用索引2.类似地,如果WHERE字句的查询条件里使用了函数(如:WHERE DAY(column)=...),MYSQL将无法使用索引3.在JOIN操作中(需要从多个数据表提取数据时),MYSQL只有在主键和外键的数据类型相同时才能使用索引,否则即使建立了 索引也不会使用4.如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP,MYSQL只有在搜索模板的第一个字符不是通配符的情况下才

Mysql中的count()与sum()区别详细介绍

首先创建个表说明问题 复制代码 代码如下: CREATE TABLE `result` ( `name` varchar(20) default NULL, `subject` varchar(20) default NULL, `score` tinyint(4) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 插入一些数据, 复制代码 代码如下: insert into result values ('张三','数学',90), ('张三'

MySql索引详细介绍及正确使用方法

MySql索引详细介绍及正确使用方法 1.前言: 索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点. 索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高系统的访问性能,接下来主要介绍在MySql数据库中索引类型,以及如何创建出更加合理且高效的索引技巧. 注:这里主要针对的是InnoDB存储引擎的B+Tree索引数据结构 2.索引的优点 1.大大减轻了服务器需要扫描的数据量,从而提高了数据的检索速度 2.帮助服务器避免排序和临时表 3.可以将

Mysql索引详细介绍

Mysql索引概述 所有MySQL列类型可以被索引.对相关列使用索引是提高SELECT操作性能的最佳途径.根据存储引擎定义每个表的最大索引数和最大索引长度.所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节.大多数存储引擎有更高的限制. 在MySQL 5.1中,对于MyISAM和InnoDB表,前缀可以达到1000字节长.请注意前缀的限制应以字节为单位进行测量,而CREATE TABLE语句中的前缀长度解释为字符数.当为使用多字节字符集的列指定前缀长度时一定要加以考虑. 还可以创建

mysql 索引详细介绍

mysql 索引详解: 在mysql 中,索引可以分为两种类型 hash索引和 btree索引. 什么情况下可以用到B树索引?  1.全值匹配索引 比如: orderID="123" 2.匹配最左前缀索引查询  比如:在userid 和 date字段上创建联合索引. 那么如果输入 userId作为条件,那么这个userid可以使用到索引,如果直接输入 date作为条件,那么将不能使用到索引. 3.匹配列前缀查询 比如: order_sn like '134%' 这样可以使用到索引. 4

mysql性能优化之索引优化

作为免费又高效的数据库,mysql基本是首选.良好的安全连接,自带查询解析.sql语句优化,使用读写锁(细化到行).事物隔离和多版本并发控制提高并发,完备的事务日志记录,强大的存储引擎提供高效查询(表记录可达百万级),如果是InnoDB,还可在崩溃后进行完整的恢复,优点非常多.即使有这么多优点,仍依赖人去做点优化,看书后写个总结巩固下,有错请指正. 完整的mysql优化需要很深的功底,大公司甚至有专门写mysql内核的,sql优化攻城狮,mysql服务器的优化,各种参数常量设定,查询语句优化,主

MySql 存储引擎和索引相关知识总结

存储引擎 什么是数据库存储引擎? 数据库引擎是数据库底层软件组件,不同的存储引擎提供不同的存储机制,索引技巧,锁定水平等功能,使用不同的数据库引擎,可以获得特定的功能 如何查看引擎? --如何查看数据库支持的引擎 show engines; ​ --查看当前数据的引擎: show create table 表名\G ​ --查看当前库所有表的引擎: show table status\G 建表时指定引擎 create table yingqin (id int,name varchar(20))

MySQL中的联合索引学习教程

联合索引又叫复合索引.对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 两个或更多个列上的索引被称作复合索引. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引 不同于使用两个单独的索引.复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏

浅谈MySQL的B树索引与索引优化小结

MySQL的MyISAM.InnoDB引擎默认均使用B+树索引(查询时都显示为"BTREE"),本文讨论两个问题: 为什么MySQL等主流数据库选择B+树的索引结构? 如何基于索引结构,理解常见的MySQL索引优化思路? 为什么索引无法全部装入内存 索引结构的选择基于这样一个性质:大数据量时,索引无法全部装入内存. 为什么索引无法全部装入内存?假设使用树结构组织索引,简单估算一下: 假设单个索引节点12B,1000w个数据行,unique索引,则叶子节点共占约100MB,整棵树最多20

mysql重复索引与冗余索引实例分析

本文实例讲述了mysql重复索引与冗余索引.分享给大家供大家参考,具体如下: 重复索引:表示一个列或者顺序相同的几个列上建立的多个索引. 冗余索引:两个索引所覆盖的列重叠 冗余索引在一些特殊的场景下使用到了索引覆盖,所以比较快. 场景 比如文章与标签表 +--+---+--+ | id | artid | tag | +--+---+--+ | 1 | 1 | PHP | | 2 | 1 | Linux | | 3 | 2 | MySQl | | 4 | 2 | Oracle | +--+---