关于女神SQLite的疑惑(2)

还是女神SQLite的话题,继续讨论有关她的种种常见疑惑。

1.问:女神SQLite是线程安全的吗?

1.答:SQLite是线程安全的,这点确凿无疑。但我要补充的一句话是:线程有时候是恶魔,不要让女神轻易接近他!

说线程是恶魔可能有点危言耸听的味道,难道线程不是我们广大编程群众喜闻乐见的基本工具么?都用了多少年啦没啥问题!的确如此,但世界上总有头上长角的牛人,可以在早已被认为平平无奇的地方硬生生找出普通人发现不了的深层逻辑谬误,并且能装订成册警示后人,来膜拜下:

www2.eecs.berkeley.edu/Pubs/TechRpts/2006/EECS-2006-1.pdf

言归正传,SQLite本身是线程安全的,但要获得这个技能,你必须在编译的时候定义宏 SQLITE_THREADSAFE 为1。如果你不确定即将链接到你程序的 SQLite 库文件是否拥有了线程安全技能,你可以调用以下函数来确认。

sqlite3_threadsafe()

SQLite 使用互斥锁来确保多线程可以顺序地访问普通数据结构,从而确保安全性。然而,频繁地索取和释放这些互斥锁势必会轻微地降低 SQLite的性能。因此,如果你不需要 SQLite 为你提供线程安全的保障,你可以用下面的编译选项来关闭它们以获得最高性能

-DSQLITE_THREADSAFE=0

另外要额外提醒一句,在 Unix/Linux 系统下,不要将一个打开的 SQLite 数据库连接,通过调用 fork() 函数传递到子进程去使用,谨记。

2.问:怎么列出一个数据库中所有的表和索引?

2.答:这分两种情况, 使用SQLite命令行的时候; 使用C/C++编程API的时候。

第一种情况,你直接使用SQLite的内置命令 ".tables" 即可查看当前数据库中的所有表,或者使用内置命令 ".schema" 来查看当前数据库中所有的表和索引的创建语句。

第二种情况,可以在一个特殊的表 "SQLITE_MASTER" 获得所有的的表和索引。每一个SQLite数据库都有一个称为 SQLITE_MASTER 的表,它统管了数据库中所有其他的元素,它的内部定义如下:

CREATE TABLEsqlite_master(

typeTEXT,

nameTEXT,

tbl_nameTEXT,

rootpageINTEGER,

sqlTEXT

);

对于一个表来说,type域就是'table'name域就是表的名字。因此可以使用以下 SQL 语句来查询当前数据库库中所有的表:

SELECTnameFROMsqlite_master

WHEREtype='table';

对于一个索引来说,type域就是'index'name域就是索引的名字,而tbl_name域则表示该索引所在的表的名字。

对于表和索引,sql域都是创建他们的原始 SQL 语句。对于自动创建的索引(比如自动递增的主键)而言,该域为 NULL。

表 SQLITE_MASTER 是只读的,你无法对其进行诸如 UPDATE、INSERT或者DELETE。当你创建或者销毁表和索引时,SQLite 系统将自动更新它。

注意,所有的临时表都不会出现在 SQLITE_MASTER 中,临时表及其索引的 schema 将被存储在另一个被称为SQLITE_TEMP_MASTER的表中。SQLITE_TEMP_MASTER 只对创建它的程序可见,除此之外它用起来跟 SQLITE_MASTER 没有任何区别。

可以使用以下语句,来查看当前数据库中所有永久的和临时的表:

SELECT name FROM

(SELECT * FROMsqlite_masterUNION ALL

SELECT * FROMsqlite_temp_master)

WHERE type='table'

ORDER BY name;

3.问:怎么在一个表中添加和删除一个域(列)?

3.答:抱歉,作为一个正常的数据库,SQLite 不能删除表中已存在的域。

换言之,SQLite 的ALTER TABLE指令只能用来在表的末尾添加一个新的域和修改表的名称。如果你想要对表做出更加出格的行为,对不起你只能另建一张表。

例如,你有个表 t1 拥有三个域:"a"、"b" 和 "c",此时你想删除域 c ,你可以这么做:

BEGIN TRANSACTION;

CREATE TEMPORARY TABLEt1_backup(a,b);

INSERT INTO t1_backup SELECT a,b FROM t1;

DROP TABLE t1;

CREATE TABLE t1(a,b);

INSERT INTO t1 SELECT a,b FROM t1_backup;

DROP TABLE t1_backup;

COMMIT;

哇哇?!搞什么鬼为什么这么麻烦? 就不能提供一个 DELETE COLUMN 来一键删除么?

不能!因为像删除这样的面目狰狞的可怕命令,对于视安全比生命更为重要的数据库而言是不能原生支持的,记录在数据库的东西,就像胎记一般,不会因为你洗个澡就洗没了,实在不想要不嫌麻烦不怕痛可以动刀子切掉,那大家都没话说。

4.问:我在数据库中删除了很多数据,但数据库却一点儿没变小,谁出来说句公道话?

4.答:别急听我说,当你从 SQLite 数据库中删除信息时,SQLite 内部会记录这个空出来的区域,以便于下次你插入新数据时可以使用。但在你没有断开数据库链接(close)之前,这片存储区域暂时不还给操作系统。

这好像是很多收押金的APP的套路。。。

对于强迫症患者来说,这不是一件好事,他们的理想情况是,我一旦删除数据,必须要看到实实在在的数据库变小!并且一定要删多少小多少,因为这样才能感觉整个世界尽在掌握之中,怎么才能做到呢?也好办,只要一个 SQL 命令就可以了:

VACUUM;

如果你有更高的要求,你要求每次删除数据时必须强迫 SQLite 自动释放相应的存储空间,那可以使用 auto_vacuum 来达到地。

PRAGMA auto_vacuum = FULL;

但是凡事都是要付出代价的,每次严格缩减存储空间带来的后果除了使得 SQLite 系统变慢之外,在缩减空间时实际上还会产生最多两倍于已用空间大小的临时存储空间需求。

5.问:SQLite那么棒,我能不能偷偷把它用到我的商业项目中,额。。。我指的是不掏任何费用的情况下?

5.答:虽然问得略显猥琐,但答案是肯定的。

SQLite是彻底的开源,你不需要为他付出任何费用,它的作者在源码的开头处仅仅写下对使用它的人的三个“祝福”:

愿你用来行善除恶

愿你原谅自己并宽恕他人

愿你宽心与人分享,所取不多于所施。

可能你会觉得作者矫情,但请注意,SQLite 不是普通的软件,世界上所有的安卓手机和苹果手机全部都使用 SQLite,这还仅仅是手机而已,还有海量电子设备都用到了这款快准狠的数据库!想想吧!作者为了开源事业,放弃了多么大的现实利益!敬佩!

6.问:怎么在字符串中包含一个单引号?

6.答:SQL 标准使用单引号来引用字符串,因此在字符串中包含单引号是需要特殊的写法:写两遍。请看:

INSERT INTO t values('苹果''香蕉');

注意到插入的字符串中红色的一堆单引号,它表示一个单引号,因此他相当于插入了这样的字符串:

苹果'香蕉

今天先聊到这儿,后续关于SQLite的常见问题会陆续更新。欢迎小伙伴关注、转发、点赞、收藏、吐槽、扔鸡蛋……

本文来自企鹅号 - 秘籍酷媒体

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏架构师之路

或许你不知道的10条SQL技巧

这几天在写索引,想到一些有意思的TIPS,希望大家有收获。 一、一些常见的SQL实践 (1)负向条件查询不能使用索引 select * from order w...

419120
来自专栏数据和云

【云和恩墨大讲堂】Oracle线上嘉年华第二讲

编辑手记:Oracle线上嘉年华,正在持续分享中。本次的主题是系统割接中的SQL解析问题和结合业务的SQL优化改写技巧。 1 嘉宾介绍 小鱼(邓秋爽) 云和恩...

30860
来自专栏逸鹏说道

大型.NET ERP系统的20条数据库设计规范

数据库设计规范是个技术含量相对低的话题,只需要对标准和规范的坚持即可做到。当系统越来越庞大,严格控制数据库的设计人员,并且有一份规范书供执行参考。在程序框架中,...

38960
来自专栏猿人谷

【黑魔法】Covering Indexes、STRAIGHT_JOIN

今天给大家介绍两个黑魔法,这都是压箱底的法宝。大家在使用时,一定要弄清他们的适用场景及用法,用好了,就是一把开天斧,用不好那就是画蛇添足。自从看过耗子哥(左耳朵...

13420
来自专栏CSDN技术头条

MySQL查询优化之道

查询优化器的任务是发现执行 SQL 查询的最佳方案。大多数查询优化器,要么基于规则、要么基于成本。

18540
来自专栏数据和云

如何编写更好的SQL查询:终极指南(上)

结构化查询语言(SQL)是数据挖掘分析行业不可或缺的一项技能,总的来说,学习这个技能是比较容易的。对于SQL来说,编写查询语句只是第一步,确保查询语句高效并且适...

30960
来自专栏大数据文摘

文本分析了4000万条Stack Overflow讨论帖,这些是程序员最推荐的编程书(附代码)

21430
来自专栏杨建荣的学习笔记

一个简单的sql审核案例 (r8笔记第90天)

今天开发的同学发来一封邮件,希望我帮忙对一个sql语句做一个评估。他们也着急要用,但是为了稳妥起见,还是希望我来审核一下,这是一个好的习惯。 打开邮件,看到的语...

31560
来自专栏andychai

MySQL模糊查询性能优化

根据模糊查找的业务场景,比对一下上面列出的6种条件,如果你的场景是全都要支持,并且是 大用户量, 接口qps高,海量的数据检索量,那就不要在数据库上做任何挣扎了...

9.4K70
来自专栏更流畅、简洁的软件开发方式

为or、in平反——or、in到底能不能利用索引?

  先说一个笑话,作为开场白。俺也换换风格试一试,呵呵。   在以前,有三个书生赶考,在路上遇到了一个算命先生,于是就问算命先生:我们三个人赶考,结果如何呀?...

252100

扫码关注云+社区

领取腾讯云代金券