MySQL table with fixed number of rows?

Question

I have table documents (id, name, time). Is there a special sql command to set table limit by 10 rows?

Answer 1

So, if you have a table like this:

CREATE TABLE documents (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR( 99 ) NOT NULL,
  `time` DATETIME NOT NULL
) ENGINE = MYISAM

then you can use this horrible query to limit row numbers by recycling the row having the lowest id field:

INSERT INTO documents (id,name,time)
  SELECT
    IF( (SELECT COUNT(*) FROM documents) < 3 -- max row number you allow
       ,NULL -- just use autoincrement id
       ,(SELECT MIN(id) FROM documents) -- update row with smallest id
    ),
    -- your values to insert
    'name'
    ,'2011-11-11'
  ON DUPLICATE KEY UPDATE
    id   = (SELECT MAX(id)+1 FROM documents) -- new id
    -- your values again, now for update
   ,name = 'name'
   ,time = '2011-11-11'

Somebody please confirm if this query is atomic, i think it is, but who knows…

Answer 2

If you just want to display the "latest 10 generated documents", no need for a separate table. Just use a query on your existing table:

SELECT id, name, `time`
FROM documents
ORDER BY `time` DESC
LIMIT 10

Answer 3

no you could not set a limit on the mysql table, you can achive this with trigger that delete rows.