5.1.1 MySQL Design Limitations/Tradeoffs

When using the MyISAM storage engine, MySQL uses extremely fast table locking (multiple readers / single writers). The biggest problem with this table type is a if you have a mix of a steady stream of updates and slow selects on the same table. If this is a problem with some tables, you can use another table type for these. See section 7 MySQL Table Types.

MySQL can work with both transactional and non-transactional tables. To be able to work smoothly with non-transactional tables (which can't rollback if something goes wrong), MySQL has the following rules:

• All columns have default values.
• If you insert a 'wrong' value in a column like a NULL in a NOT NULL column or a too big numerical value in a numerical column, MySQL will instead of giving an error instead set the column to the 'best possible value'. For numerical values this is 0, the smallest possible values or the largest possible value. For strings this is either the empty string or the longest possible string that can be in the column.
• All calculated expressions returns a value that can be used instead of signaling an error condition. For example 1/0 returns NULL

For more information about this, see See section 1.8.5 How MySQL deals with constraints.

The above means that one should not use MySQL to check fields content, but one should do this in the application.

User Comments

Add your own comment.