限制SQLite中的行数

1 回答

• 1

  我知道一种有效的方法，但它有点难看 . 它依赖于精心构造的约束和播种数据库 . 为简洁起见，我只用了五行而不是50行 .

  create table test (
    row_num integer primary key
      check ((round(row_num) = row_num) and (row_num between 1 and 5)),
    other_columns char(1) not null default 'x',
    row_timestamp timestamp 
      not null unique 
      default current_timestamp
  );
  

  表达式 round(row_num = row_num) 保证row_num列中有整数 . 否则，SQLite会让你在那里插入1.54或'wibble' .

  other_columns 列只是实际数据的占位符 .

  insert into test (row_num, row_timestamp) values
  (1, '2015-01-01 08:00:01'),
  (2, '2015-01-01 08:00:02'),
  (3, '2015-01-01 08:00:03'),
  (4, '2015-01-01 08:00:04'),
  (5, '2015-01-01 08:00:05');
  

  实际的时间戳值并不意味着什么 . 不管怎样，还没有 . 像这样播种数据库意味着，从现在开始，您只需要执行更新语句 . 如果表开始时为空，则必须处理插入和更新的不同逻辑 . 例如，您必须计算行以确定是插入还是更新 .

  create trigger update_timestamp
  after update on test 
  for each row
  begin
    update test 
    set row_timestamp = strftime('%Y-%m-%d %H:%M:%f', 'now')
    where row_num = OLD.row_num;
  end;
  

  "update_timestamp"触发器使SQLite以几分之一秒（ %f ）维护时间戳 . 可能取决于底层操作系统是否支持分数精度 .

  create trigger no_deletes
  after delete on test
  for each row
  begin
    -- There might be a more elegant way to prevent deletes.
    -- This way just inserts exactly what a delete statement deletes.
    insert into test (row_num, other_columns, row_timestamp) 
    values (OLD.row_num, OLD.other_columns, OLD.row_timestamp);
  end;
  

  ---

  现在您可以更新数据了 . 您更新自己的数据，这里只是占位符 other_columns ，SQLite负责其余的数据 .

  update test 
  set other_columns = 'b' 
  where row_timestamp = (select min(row_timestamp) from test);
  select * from test order by row_timestamp desc;
  

  row_num     other_columns  row_timestamp          
  ----------  -------------  -----------------------
  1           b              2015-03-08 12:43:21.926
  5           x              2015-01-01 08:00:05    
  4           x              2015-01-01 08:00:04    
  3           x              2015-01-01 08:00:03    
  2           x              2015-01-01 08:00:02
  

  回复于 2024-04-26T15:35:46+08:00