See also - MySQL — a database overview: basics, purpose,

Это окончание невероятной информации про mysql.

...

always lets you get around these problems by including simple checks before modifications and running simple scripts that check database integrity and even perform repairs automatically. Note that just by using the MySQL log file, or even adding one extra log file, you can usually recover tables without losing data integrity.

In addition, fatal modifications in a transactional scheme can be redesigned so as to become atomic. In fact, virtually all the integrity issues that transactions solve can be handled using LOCK TABLES or atomic modifications, ensuring that you never get an automatic database crash, which is a common problem for transactional databases.

Far from all transactions can prevent data loss if the server crashes. In such cases even a transactional system can lose data. No system is 100% safe — it's only a matter of minimizing losses. Even Oracle, it is reported, sometimes loses data in such situations, even though it is considered the safest of the transactional databases.

To ensure safety in MySQL, you only need to have backups and a log of modifications. With this, you can recover from virtually any database damage.

The transactional paradigm has benefits and drawbacks. Many users and application developers rely on the ease with which they can work around problems where a crash occurs or is needed, and they will probably have to do a bit more work with MySQL to think differently or write more code. If you are unfamiliar with the atomic operations paradigm or more familiar with transactions, don't assume that MySQL is unfamiliar with these issues. Reliability and integrity come first for the authors of this package! Recent estimates indicate there are more than 1,000,000 mysqld servers, many of which are in production environments. It is very rare to hear from users that they lost data, and in almost all cases the users themselves were at fault. This is the best proof of MySQL's stability and reliability.

Finally, in situations where integrity is of the highest importance, MySQL's current features provide a level of transaction-like or even better reliability and integrity. If you lock tables using LOCK TABLES, all modifications stop until any integrity checks are done. If you only obtain a read lock (as opposed to a write lock), then reads and inserts continue to work. Newly inserted records will not be seen by clients holding a READ lock until they release their read locks. Using INSERT DELAYED you can place inserts into a local queue, where they remain until the locks are released. This way, the server won't have a user waiting for the insert to finish. Details in section "8.4 INSERT DELAYED syntax".

"Atomic" means that you can be sure that while each specific modification is being performed, no other user can collide with it, and there will never be an automatic rollback (although this can happen on transactional systems if you're not very careful). MySQL also guarantees that there will be no dirty reads. You can find an example of how to write atomic modifications in section "1.2.6 How to cope without COMMIT/ROLLBACK".

Using the atomic paradigm allows many performance optimizations that would not otherwise be possible. In addition, with a sensible approach this scheme speeds up work 3-5 times compared to the best transactional databases at the same level of reliability.

For those cases where safety is more important than performance, I recommend using transactional table types BDB or InnoDB for all critical data.

One final note: the authors of the package are currently working on a safe replication scheme that should be better than any replication support known today. This system will work most reliably with atomic operations rather than transactions.

1.2.4.4 Stored procedures and triggers

A stored procedure is a set of SQL commands that can be compiled and stored on the server. Once this has been done, the client doesn't need to send the whole query, but can refer to the stored procedure. This provides better efficiency because the query only needs to be parsed once, and less information needs to be sent between the client and server. You can also raise the conceptual level by having libraries of functions.

A trigger is a stored procedure that is invoked when a specific event occurs. For example, you can set up a stored procedure that will be invoked every time a record is deleted from the transaction table. This procedure automatically removes the corresponding customer from the customer table when all their transactions have been deleted.

The planned modification language will be able to handle stored procedures, but without triggers. Triggers usually slow everything down, even queries that are unrelated to them.

1.2.4.5 Foreign keys

Note that foreign keys in SQL are not used to join tables, but are typically used to check referential integrity. If you want to get a result from several tables with a SELECT command, you do it by joining the tables like this:

SELECT * from table1,table2 where table1.id = table2.id;

Details are in sections "8.1.1 JOIN syntax" and "2.5.6 Using foreign keys".

The FOREIGN KEY syntax in MySQL exists only for compatibility with other versions of the SQL CREATE TABLE command — it doesn't do anything. The FOREIGN KEY syntax without ON DELETE ... is usually used for documentation purposes. Some ODBC-standard applications may use this to produce automatic WHERE clauses, but this is usually simple enough to override. FOREIGN KEY is sometimes used as a constraint check, but this check is practically unnecessary if rows are inserted into tables in the correct order. MySQL supports these clauses only because some applications require them to exist (regardless of whether they work or not).

In MySQL you can work around the problem of the non-functional ON DELETE ... construct by adding the corresponding DELETE statement to the application when you delete records from a table that has a foreign key. In practice this is sometimes faster and much more portable than using foreign keys in the table.

In the near future we will extend the FOREIGN KEY implementation so that at least the information is stored in the table specification file and can be retrieved by mysqldump and ODBC. At a later stage we will implement foreign key constraints for applications that cannot easily be recoded to avoid them.

1.2.4.6 Why foreign key support is not implemented

Many database theorists and programmers feel that referential integrity should be enforced within the database server. Indeed, in many cases this approach is very useful. However, in conversations with many database users, the authors have observed that foreign keys are often misused, which can cause serious problems. Even when used correctly, they are not a magic solution to the referential integrity problem, although they do make things simpler in some cases.

Because of the above observations, the authors have not given foreign key implementation a high priority. However, in recent years the user base has expanded, and now the authors of the package have many users who would like to have enforced referential integrity support within MySQL. So foreign keys will be implemented in the near future after all.

Some advantages of using foreign keys:

• With an appropriate relational design, foreign key constraints make it harder for the programmer and user to create inconsistencies in the database.
• Using cascading modifications and deletes can simplify application-level code.
• A properly designed foreign key helps document the relationships between tables.

Drawbacks:

• MySQL does not yet support enforced referential integrity, so if your application depends on this, you won't be able to use MySQL until this feature is implemented.
• Errors that are simple in the design of key relationships can cause serious problems, for example circular rules or an incorrect combination of cascading deletes.
• A properly written application will itself make sure it doesn't violate integrity constraints before it starts processing a query. Thus, additional checks at the database level would only slow down performance for such an application.

1.2.4.7 Views

MySQL does not support views, but this is planned to be fixed around 4.1.

Views are usually useful for allowing users to access a set of relations as a single table (in read-only mode). Many SQL databases don't allow modifying any rows in such a view: you have to make all modifications on the individual tables.

MySQL is typically used in applications and web systems where the application's author has full control over the use of the database. For this reason views are not considered very important.

To restrict access to columns in MySQL, views aren't needed either: MySQL has a very sophisticated privilege-granting system. Details in section "10 General security issues and the MySQL access privilege system".

1.2.4.8 `--' as the start of a comment

Some SQL databases use -- as the start of a comment. MySQL has # as the comment start character, even though the mysql command-line tool removes all lines starting with --. You can also use C-style comments (/* this is a comment */) in MySQL.

MySQL Version 3.23.3 and above supports the -- comment style, but only if the comment is followed by a space. This is because this comment style caused a lot of problems with automatically generated SQL queries that used something like the following code, where we automatically substitute the payment value for !payment!:

UPDATE tbl_name SET credit=credit-!payment!

What do you think happens when the payment value is negative? Here's what: since 1--1 is valid in SQL, the package thinks that a ---style comment has begun. That's hardly what you intended...

In MySQL Version 3.23 you can use: 1-- This was a comment

The following discussion applies to you only if you're running MySQL Version 3.23 or earlier:

If you have an SQL program in a text file that contains -- comments, you should use:

shell> replace " --" " #" < text-file-with-funny-comments.sql \
                   | mysql database

Instead of the usual solution:

shell> mysql database < text-file-with-funny-comments.sql

You can also edit the command file to change the -- comments to #:

shell> replace " --" " #" -- text-file-with-funny-comments.sql

Change them back with this command:

shell> replace " #" " --" -- text-file-with-funny-comments.sql

1.2.5 What standards does MySQL comply with?

Entry level SQL92. ODBC levels 0-2.

1.2.6 How to cope without COMMIT/ROLLBACK

The following generally applies only to ISAM, MyISAM, and HEAP tables. If you only use transaction-safe tables (BDB or InnoDB) for modifications, you can also do COMMIT and ROLLBACK in MySQL. Details in section "9.2.1 BEGIN/COMMIT/ROLLBACK syntax".

The problem with efficiently handling COMMIT-ROLLBACK with the above-mentioned table types requires a completely different table layout than the one MySQL uses today. The table type would also need additional threads to perform automatic cleanups on the tables, and disk usage would be much higher. This would make these table types roughly 2-4 times slower than they are now.

The current problem is ROLLBACK. Without ROLLBACK you can do any kind of COMMIT using LOCK TABLES. To support ROLLBACK with the above-mentioned table types, MySQL would need to be changed to store all the old records that were modified, and be able to quickly return to the starting point if a ROLLBACK command was issued. For simple cases this is fairly straightforward (something like isamlog could be adapted for this), but it would be much harder to implement ROLLBACK for ALTER/DROP/CREATE TABLE.

To avoid using ROLLBACK, you can use the following strategy:

1. Apply LOCK TABLES ... to lock all the tables you want to access.
2. Check all conditions.
3. Modify, if everything is in order.
4. Call the UNLOCK TABLES command to release the locks.

This is usually a much faster method than using transactions with ROLLBACK capability, though not always. The only situation this solution doesn't handle is when someone kills a thread in the middle of a modification. In this case all locks will be released, but some of the modifications may not have been performed.

You can also use functions to modify records in a single operation. You can get a very efficient application by applying the following techniques:

• Change fields relative to their current value.
• Modify only those fields that actually changed.

For example, when we make modifications to some customer information, we only modify the customer data that changed, and we check that none of the changed data — or other data that depends on the changed data — has changed compared to the original row. The test for changed data is performed with a WHERE clause in the UPDATE statement. If the record wasn't modified, we give the user a message saying that some of the data they changed was changed by another user. Then we show the old row against the new row in a window, so the user can decide which version of the customer record they should use.

This gives us something similar to column locking, but it's actually even better because we only modify some of the columns, using values computed relative to their current values. This means that typical UPDATE statements look roughly like this:

UPDATE tablename SET pay_back=pay_back+'relative change';
UPDATE customer SET customer_date='current_date',
                    address='new address', phone='new phone',
                    money_he_owes_us=money_he_owes_us+'new_money'
       WHERE customer_id=id AND address='old address' AND phone='old phone';

As you can see, this is very efficient and works even if another user has changed the values of the pay_back or money_he_owes_us columns.

In many cases users wanted to use ROLLBACK and/or LOCK TABLES for the purpose of managing unique identifiers for some tables. This can be handled much more efficiently using an AUTO_INCREMENT column and the SQL function LAST_INSERT_ID() or the C API function mysql_insert_id().

At MySQL AB the authors of the package have never had any need for row-level locking, because they could always work around it. Some cases genuinely do need row locking, but there are very few of them. If you want row-level locking, you can use a flag column in the table and do something like:

UPDATE tbl_name SET row_flag=1 WHERE id=ID;

MySQL will return the number of rows processed if the row was found, and row_flag was not 1 in the original row.

1.2.7 Known errors and issues

The issues listed below are known to the authors of the package, and fixing them is a very high priority.

• ANALYZE TABLE on BDB tables can in some cases render the table unusable until you restart mysqld. When this happens, you will see errors like the following in the MySQL error log:

  001207 22:07:56 bdb: log_flush: LSN past current end-of-log
  

• Do not run ALTER TABLE on a BDB table on which you have unfinished multi-statement transactions. The transaction will probably be ignored.
• ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE can cause problems on tables for which you're using an INSERT DELAYED call.
• Running LOCK TABLE ... and FLUSH TABLES ... doesn't yet guarantee that there isn't a half-finished transaction.
• BDB tables aren't quick to open. If you have many BDB tables in a database, it will take a long time to use the mysql client on that database unless you use the -A option or use rehash. This is especially important when you have a large table cache.
• The current replication protocol cannot deal with LOAD DATA INFILE and with aligning line-terminator characters that themselves take up more than 1 character.

The following issues are known and will be fixed in due course:

• MATCH only works with SELECT statements.
• When using SET CHARACTER SET, transliterated characters cannot be used in the database, table, and columns.
• DELETE FROM merge_table, used without WHERE, will only clear the mapping for the table without deleting anything in the mapped tables themselves.
• You cannot build the package in a different directory when using MIT-pthreads, since this requires modifying the MIT-pthreads code — we're unlikely to fix this feature.
• BLOB values cannot be reliably used in GROUP BY, ORDER BY, or DISTINCT. Only the first max_sort_length bytes (1024 by default) are used when comparing BLOB values in these cases. This can be changed with the -O max_sort_length option when starting mysqld. A workaround for most cases is to use a substring: SELECT DISTINCT LEFT(blob,2048) FROM tbl_name.
• Computation is performed with BIGINT or DOUBLE (both usually 64 bits long). This depends on the function being handled by the package. General rule: bit functions are performed with BIGINT precision, IF and ELT() with BIGINT or DOUBLE, and everything else with DOUBLE. You should try to avoid using large unsigned long values without a sign (9223372036854775807)!
• All string columns except BLOB and TEXT automatically strip all trailing spaces when stored. For the CHAR type this is allowed and can be considered a feature per ANSI SQL92. The bug is that in MySQL VARCHAR columns are treated the same way.
• You can only have up to 255 columns of type ENUM and SET per table.
• safe_mysqld redirects all mysqld messages to the mysqld log file. One problem with this is that if you run mysqladmin refresh to close and reopen the log file, stdout and stderr are still redirected to the old log file. If you use the --log option, you should edit safe_mysqld to log data to the file 'hostname'.err instead of 'hostname'.log so you don't have major problems running mysqladmin refresh.
• In an UPDATE statement, columns are modified left to right. If you reference a column being modified, you get the modified value instead of the original value. For example:

  mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1;
  

  This modifies KEY by 2 instead of 1.
• You cannot use a temporary table more than once in the same query. For example, the following won't work:

  select * from temporary_table, temporary_table as t2;
  

• RENAME doesn't work with TEMPORARY tables.
• The optimizer may handle DISTINCT differently depending on whether you use hidden columns in a join or not. In a join, hidden columns are counted as part of the result (even though they're not shown), whereas in a normal query they don't participate in the DISTINCT comparison. We will probably change this in the future so that hidden columns are never compared when performing DISTINCT. For example:

  SELECT DISTINCT mp3id FROM band_downloads WHERE userid=9 ORDER BY id DESC;
  

  and

  SELECT DISTINCT band_downloads.mp3id, FROM band_downloads,band_mp3
         WHERE band_downloads.userid=9 AND band_mp3.id=band_downloads.mp3id
         ORDER BY band_downloads.id DESC;
  

  In the second case, in MySQL 3.23.x you may get two identical rows in the result set (because the hidden 'id' column may differ). Note that this only happens for queries where you don't have an ORDER BY in the result, which is actually incorrect from the ANSI SQL standard's point of view.
• Since MySQL allows you to work with table types that don't support transactions (and thus cannot perform rollback), some things behave slightly differently than in other SQL servers. This is only meant to guarantee that MySQL never has to roll back SQL commands. This can sometimes be a bit clumsy, since column values must be checked by the application, but this actually gives you a nice performance boost, since it allows MySQL to make some optimizations that would otherwise be impossible or very difficult. If you set a column to an incorrect value, MySQL will, instead of rolling back, store the best possible value in the column.
  • If you try to store an out-of-range value in a numeric column, MySQL will instead store the smallest or largest possible value.
  • If you try to store a string that doesn't start with a number in a numeric column, MySQL will store 0 in it.
  • If you try to store NULL in a column that doesn't accept NULL values, MySQL will store 0 or '' (an empty string). This behavior can, however, be changed with the compile option -DDONT_USE_DEFAULT_FIELDS.
  • MySQL allows you to store some incorrect date values in DATE and DATETIME columns. For example, 2000-02-31 or 2000-02-00. If the date is completely invalid, MySQL will store the special date value 0000-00-00 in the column.
  • If you set an enum to an unsupported value, it will be set to the 'empty string' error value with a numeric value of 0.
• If you run PROCEDURE on a query that returns an empty set, in some cases PROCEDURE won't transform the columns.
• Creating a MERGE type table doesn't check whether the underlying tables have compatible types.
• MySQL still cannot handle NaN, -Inf, and Inf values in a double. Using them will cause problems when trying to export and import data. As an interim solution, you should change NaN to NULL (if possible), and -Inf and Inf to the minimum and maximum possible double values respectively.
• LIMIT with negative numbers turns into very large positive numbers, which is a bug.
• If you use ALTER TABLE to first add a UNIQUE index to a table used in a MERGE-type table, and then use ALTER TABLE to add a normal index directly on the MERGE table, the key order will differ between the tables if there was an old non-unique key in the table. This is because ALTER TABLE puts UNIQUE keys before normal keys, in order to detect duplicate keys as early as possible.

The following are known bugs in earlier versions of MySQL:

• You can get a hung thread if you do a DROP TABLE on a table that is one of several tables locked with LOCK TABLES.
• In the following cases you can get a core dump:
  • The delayed insert handler has pending inserts to process for the table.
  • LOCK table with WRITE
  • FLUSH TABLES
• Before MySQL Version 3.23.2, an UPDATE that modified a key using a WHERE on that same key could possibly fail, because the same row was used for the lookup:

  UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100;
  

  You can work around this like so:

  mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100;
  

  This works because MySQL won't use an index on expressions in the WHERE clause.
• Before MySQL Version 3.23, all numeric types were treated as fixed-point fields. This meant you had to specify how many decimal digits should appear after the decimal point. All results were returned with the correct number of decimal digits.

To look into platform-specific bugs, see the sections on compiling and porting.

Comparison table of major relational databases

See also

• DBMS
• SQL [[b2557]]
• NoSQL [[b8218]]
• MSSQL
• Oracle
• [[b6837]]
• MariaDB
• phpMyAdmin
• MySQL Workbench
• Firebird

Продолжение:

Часть 1 MySQL — a database overview: basics, purpose, and capabilities
Часть 2 See also - MySQL — a database overview: basics, purpose,

See also

• Relational database language SQL Lecture 13. SQL language. Functions and main features
• NoSQL (not only SQL)
• PostgreSQL - database overview, basics