doulun5683 2011-10-16 12:06
浏览 608
已采纳

Sqlite3最大主键值

I have table with INTEGER PRIMARY KEY and I cannot insert value greater then 2147483647 as a key. Is it possible to increase this value somehow?

UPDATE: Ok, I have php script:

<?php

$sqlite_db = new SQLite3('test');
$sqlite_db->exec('CREATE TABLE test (n UNSIGNED INTEGER)');

$ns = array(1111111111,2222222222,3333333333,4444444444,5555555555,6666666666,7777777777);

foreach ($ns as $n)
{
    $statement = $sqlite_db->prepare('INSERT INTO test (n) VALUES (:n)');
    $statement->bindValue(':n', $n, SQLITE3_INTEGER);
    $statement->execute();
}

$sqlite_db->close();
?>

and what I get in test database is that:

root@localhost:~# sqlite3 test 
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from test;
1111111111
-2072745074
-961633963
149477148
1260588259
-1923267926
-812156815

So my question is: how to insert large values into sqlite database from php?

UPDATE2:

I have changed foreach body to:

$sqlite_db->exec("INSERT INTO test (n) VALUES ({$n})");

and it works ok now.

  • 写回答

2条回答 默认 最新

  • duancilan5124 2011-10-16 12:46
    关注

    I've never seen that behavior with SQLite3. Edit your question, and paste the output of .schema your-table-name. Also, make sure the problem isn't in your application code.

    Declaring a column as integer in SQLite works differently than declaring a column as integer in a true SQL dbms. In SQLite3, any column declared INTEGER PRIMARY KEY ought to accept any 64-bit, signed integer. 64-bit integers max go to 1.84467441 × 1019. (Your column should accept negative integers, too.)

    INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

    sqlite> create table t ( n integer primary key, s varchar(35));
    sqlite> insert into t (s) values ('a');
    sqlite> insert into t (s) values ('a');
    sqlite> insert into t (s) values ('a');
    sqlite> select * from t;
    1|a
    2|a
    3|a
    

    So, it automatically increments . . .

    sqlite> insert into t values (2147483647, 'a');
    sqlite> select * from t;
    1|a
    2|a
    3|a
    2147483647|a
    

    And it accepts the maximum value of a 32-bit signed integer.

    sqlite> insert into t (s) values ('a');
    sqlite> insert into t (s) values ('a');
    sqlite> insert into t (s) values ('a');
    sqlite> select * from t;
    1|a
    2|a
    3|a
    2147483647|a
    2147483648|a
    2147483649|a
    2147483650|a
    

    And it increments past the maximum value of a 32-bit signed integer.

    sqlite> insert into t values (8589934592, 'a');
    sqlite> select * from t;
    1|a
    2|a
    3|a
    2147483647|a
    2147483648|a
    2147483649|a
    2147483650|a
    8589934592|a
    

    It accepts values in the range of 233.

    sqlite> insert into t (s) values ('a');
    sqlite> select * from t;
    1|a
    2|a
    3|a
    2147483647|a
    2147483648|a
    2147483649|a
    2147483650|a
    8589934592|a
    8589934593|a
    

    And it continues to automatically increment.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 python天天向上类似问题,但没有清零
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 C#调用python代码(python带有库)
  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)