12/13/2023 0 Comments Mysql limit rows![]() ![]() This is documented to work for InnoDB in MySQL since 5.7.8, but it definitely does not work in MariaDB 10.1 as of this writing.Įdited to add: This is not foolproof. Note: While it may seem possible to use a generated virtual column to avoid taking up the extra byte of storage for the restricting enum, unfortunately most versions of MySQL and MariaDB will throw various errors if you try it, either because a constant value can not be used for a generated column, or if you trick the database into allowing that (for example, using GENERATED ALWAYS AS (LEAST(GREATEST(single_row_value,0),0)) VIRTUAL), because storage engines don't like placing indexes on virtual generated columns. (Of course, where N is one, as in this example, the same effect is achieved.) However, I could not find a way to enforce that a table have either 0 or N rows this solution will only enforce "between 0 and N", as MySQL does not have working CHECK constraints. The limit in MySQL 5.7 is effectively 255 cases, but by that point it would be easier to take the UNQIUE KEY over a TINYINT UNSIGNED column instead. This could be extended in the obvious fashion to create tables which can only contain between 0 and any specific number of rows, up to the practical limits of an ENUM. ![]() This works because the column can only ever have a single value (the ENUM has only a single case, and can not be NULL), and the UNIQUE (or PRIMARY) key enforces that every row in the table must have a different value for the column. Mysql> INSERT INTO only_one_row (single_row_value) VALUES ('') ĮRROR 1062 (23000): Duplicate entry '' for key 'PRIMARY" ![]() Mysql> INSERT INTO only_one_row (single_row_value) VALUES (NOW()) While other answers are correct that there are better approaches to your particular question, it is possible to create a single-row table by adding a dummy enumeration column with only a single case, and making it a UNIQUE KEY (or the table's PRIMARY KEY): mysql> CREATE TABLE only_one_row ( ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |