sqlite where 語句的 like 和 = 相差多少

 1 #!/usr/bin/env python 2  3 ''' 4 genearte the sqlite db 5 ''' 6 import time 7 import apsw 8  9 10 LOOP_INSERT = 1000011 LOOP_SEARCH = 100012 13 def genDB():14     loop_time = LOOP_INSERT15     db = apsw.Connection(":memory:")16     cursor = db.cursor()17     cursor.execute("create table big(itemall string);")18     cursor.execute("begin;")19     for i in xrange(loop_time):20         cursor.execute("insert into big (itemall) values (%010d); " % i)21 22     cursor.execute("commit;")23 24     cursor.execute("create table small(idx int primary key, v1 int, v2 char);")25     cursor.execute("begin;")26     for i in xrange(loop_time):27         cursor.execute("insert into small (v1, v2) values (%d, %010d); " % (i, i))28 29     cursor.execute("commit;")30 31     return db32 33 34 def test1(db):35     c = db.cursor()36     start = time.time()37     for i in xrange(LOOP_SEARCH):38         c2 = c.execute("select * from big where itemall like '%010d' LIMIT 1;" % i)39         values = c2.fetchall()40         if len(values) > 0:41             pass42     end = time.time()43     44     print "duration test1 ", end - start45     46 47 def test2(db):48     c = db.cursor()49     start = time.time()50     for i in xrange(LOOP_SEARCH):51         c2 = c.execute("select * from small where v1 = '%010d' and v2 = %10d LIMIT 1;" % (i, i))52         values = c2.fetchall()53         if len(values) > 0:54             pass55     end = time.time()56     57     print "duration test2 ", end - start58         59 60 def main():61     db = genDB()62     test1(db)63     test2(db)64     65 66 if __name__ == '__main__':67     main()