使用sqlite3的時候，遇到個很奇怪的問題，select某字段的時候，報錯感覺像是該字段不存在，但是表中確實是有這個字段的。查詢其他字段均沒有問題，唯獨group這個字段
該表是 kafka監(jiān)控工具KafkaOffsetMonitor-assembly-0.2.0.jar創(chuàng)建的表，表結(jié)構(gòu)如下
sqlite> .schema OFFSETS
CREATE TABLE "OFFSETS" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"group" VARCHAR(254) NOT NULL,"topic" VARCHAR(254) NOT NULL,"partition" INTEGER NOT NULL,"offset" BIGINT NOT NULL,"log_size" BIGINT NOT NULL,"owner" VARCHAR(254),"timestamp" BIGINT NOT NULL,"creation" BIGINT NOT NULL,"modified" BIGINT NOT NULL);
CREATE INDEX "idx_search" on "OFFSETS" ("group","topic");
CREATE INDEX "idx_time" on "OFFSETS" ("timestamp");
CREATE UNIQUE INDEX "idx_unique" on "OFFSETS" ("group","topic","partition","timestamp");
查詢操作如下，select group字段報錯：Error: near "group": syntax error，但是select topic是沒問題的。
sqlite> select * from OFFSETS limit 1;
id group topic partition offset log_size owner timestamp creation modified
---------- ----------------------- ----------------------- ---------- ---------- ---------- ---------- ------------- ------------- -------------
5092201 IM_APP_NOTICE_MSG_OLD_1 IM_APP_NOTICE_MSG_TOPIC 0 1021391 1032122 1483027807567 1471691588963 1471833538219
sqlite> select * from OFFSETS where group='IM_APP_NOTICE_MSG_OLD_1';
Error: near "group": syntax error
sqlite> select * from OFFSETS where topic='IM_APP_NOTICE_MSG_TOPIC' limit 1;
id group topic partition offset log_size owner timestamp creation modified
---------- ----------------------- ----------------------- ---------- ---------- ---------- ---------- ------------- ------------- -------------
5092201 IM_APP_NOTICE_MSG_OLD_1 IM_APP_NOTICE_MSG_TOPIC 0 1021391 1032122 1483027807567 1471691588963 1471833538219
sqlite> select group from OFFSETS limit 1;
Error: near "group": syntax error
sqlite> select topic from OFFSETS limit 1;
topic
-----------------------
IM_APP_NOTICE_MSG_TOPIC
搜索了半天，也沒找到相關(guān)資料，但是無意在group字段上加引號后，居然不會報錯了。╮(╯▽╰)╭
sqlite> select "topic" from OFFSETS limit 1;
topic
-----------------------
IM_APP_NOTICE_MSG_TOPIC
遇到第二個問題：
查詢某字典時，字段值若太長，出來的結(jié)果會被截斷。但是select * 查詢出來的不會被截斷。還好python操作的時候沒有這個問題
sqlite> select DISTINCT "group" from OFFSETS ;
group
----------------------
IM_APP_NOTICE_MSG_OLD_
IM_APP_NOTICE_MSG_OLD_
>>> import sqlite3
>>> conn = sqlite3.connect('offsetapp.db')
>>> cur = conn.cursor()
>>> cur.execute('select DISTINCT "group" from OFFSETS ;')
<sqlite3.Cursor object at 0x7f508817c190>
>>> cur.fetchall()
[(u'BINSPECT_MSG_SERVICE_1',), (u'IM_APP_NOTICE_MSG_OLD_1',), (u'IM_APP_NOTICE_MSG_OLD_10',), (u'IM_APP_NOTICE_MSG_OLD_2',), (u'IM_APP_NOTICE_MSG_OLD_3',), ]
