7.1.1. MySQL Design Limitations and Tradeoffs
When using the MyISAM storage engine, MySQL
uses extremely fast table locking that allows multiple readers
or a single writer. The biggest problem with this storage engine
occurs when you have a steady stream of mixed updates and slow
selects on a single table. If this is a problem for certain
tables, you can use another storage engine for them. See
Chapter 14, Storage Engines and Table Types.
MySQL can work with both transactional and non-transactional
tables. To make it easier to work smoothly with
non-transactional tables (which cannot roll back if something
goes wrong), MySQL has the following rules. Note that these
rules apply only when not running in strict
SQL mode or if you use the IGNORE specifier
for INSERT or UPDATE.
All columns have default values.
If you insert an inappropriate or out-of-range value into a
column, MySQL sets the column to the “best possible
value” instead of reporting an error. For numerical
values, this is 0, the smallest possible value or the
largest possible value. For strings, this is either the
empty string or as much of the string as can be stored in
the column.
All calculated expressions return a value that can be used
instead of signaling an error condition. For example, 1/0
returns NULL.
To change the preceding behaviors, you can enable stricter data
handling by setting the server SQL mode appropriately. For more
information about data handling, see
Section 1.9.6, “How MySQL Deals with Constraints”,
Section 5.2.5, “The Server SQL Mode”, and Section 13.2.4, “INSERT Syntax”.
