Additional languages
MySQL 3.23, 4.0, 4.1 Reference Manual :: 7 Optimization :: 7.1 Optimization Overview :: 7.1.1 MySQL Design Limitations and Tradeoffs
« 7.1 Optimization Overview
7.1.2 Designing Applications for Portability »
Section Navigation      [Toggle]

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 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.6, “SQL Modes”, and Section 13.2.4, “INSERT Syntax”.


User Comments

Posted by E Van den Boogaard on February 14 2006 6:25pm[Delete] [Edit]

I suggest a crisper description for the next sentence on this page. The reason is that the "only ... not ... or" construcion, although correct in English, is difficult to understand for non-native-English readers. I think it means:

"Note that these rules apply *only* when not running in strict SQL mode *or* when you use the IGNORE specifier for INSERT or UPDATE."

Maybe there is another, better way to make the sentence simpler. [To be deleted].


Add your own comment.