Go to the first, previous, next, last section, table of contents.

25 How MySQL Compares to Other Databases

This chapter compares MySQL to other popular databases.

This chapter has been written by the MySQL developers, so it should be read with that in mind. There are no factual errors contained in this chapter that we know of. If you find something which you believe to be an error, please contact us about it at docs@mysql.com.

For a list of all supported limits, functions, and types, see the crash-me Web page at http://www.mysql.com/information/crash-me.php.

25.1 MySQLmSQL との比較

この節は、MySQL 開発者によって書かれていますので、そのつもりで 読んで下さい。しかし我々が知っている事実上の間違いはありません。

For a list of all supported limits, functions, and types, see the crash-me Web page.

真の速度比較のためは、増加する MySQL ベンチマークスイートを見て下さい 「13.7 Using Your Own Benchmarks」節参照. Because there is no thread creation overhead, a small parser, few features, and simple security, mSQL should be quicker at: これらのオペレーションはとても単純なので、開始のオーバーヘッドが高くなる 時にそれらを良くするのは難しいです。接続が確立された後は MySQL はとても良い性能になります。 他のものでは MySQLmSQL や多くの他の SQL 実装よりとて も速いです:
SQL Features
領域能力 (Disk Space Efficiency)
これは、テーブルをどのように小さくできるかです。 MySQL はとても精密な型を持ちます。とても小さい領域を使用するテーブルを作成でき るためです。MySQL データ型の有用な例は、3バイト長の MEDIUMINT です。10,000,000 レコードを持つ場合、1レコードあたり1バイトの節約でもと ても重要です。 mSQL2 は4つの型(char,text,int,real)しかないので、小さなテーブルを 得るのは難しいです。
これを客観的に判断するのは難しいです。MySQL の安定性については 「1.7 MySQL はどれくらい安定か?」節 を参照してください。 我々には mSQL 安定性の経験がありません。そのため、我々はこれにつ いては何も言えません。
もう一つの重要な問題はもちろんライセンスです。MySQLmSQL よりも柔軟なライセンスを持っています。そして mSQL よ りもより安いです。少なくとも、あなたが使用を選択した製品全てに、ライセン スまたは email サポートへの支払いを考慮することを覚えていて下さい。 MySQL を含めた製品を売る場合、もちろんこのライセンスの獲得が要求 されます。
Perl interfaces
MySQL は基本的に mSQL と同じ perl インタフェースと、いくつかの追 加機能を持ちます。
JDBC (Java)
MySQL currently has a lot of different JDBC drivers: The recommended driver is the mm driver. The Resin driver may also be good (at least the benchmarks looks good) but we haven't got that much information about this yet. We know that mSQL has a JDBC driver, but we have too little experience with it to compare.
MySQL はとても小さい開発チームを持っていますが、我々は C と C++ のコーディ ングをとても速く行ないます。スレッド、関数、 GROUP BY などはまだ mSQL には実装されていませんので、it has a lot of chatching up to do. この上でいくつかの見通しを得るために、去年の mSQL `HISTORY' ファ イルを見て、MySQL Reference Manual News 節と比較できます( 「F MySQL change history」節)。 速く開発された多くのものが、かなり明白になります。
mSQLMySQL は多くの興味深いサードパーティツールを持っ ています。上位への移行 (mSQL -> MySQL) はとても簡単です。 MySQL は、ほとんど全ての興味深い mSQL アプリケーションを 持っています。 MySQL には、簡単な msql2mysql プログラムが用意されています。 これは、mSQLMySQL の C API 関数のスペルの違いを修正するプログラムです。 For example, it changes instances of msqlConnect() to mysql_connect(). mSQL から MySQL への変換は通常、数分使用するだけです。

25.1.1 How to Convert mSQL Tools for MySQL

According to our experience, it would just take a few hours to convert tools such as msql-tcl and msqljava that use the mSQL C API so that they work with the MySQL C API.

The conversion procedure is:

  1. シェルスクリプト msql2mysql をソースに実行します。バイナリプログラム replace が必要です。これは MySQL で配布されます。
  2. Compile.
  3. 全てのコンパイラエラーを修復します.

MySQLmSQL 間の C API の違い:

25.1.2 How mSQL and MySQL Client/Server Communications Protocols Differ

There are enough differences that it is impossible (or at least not easy) to support both.

The most significant ways in which the MySQL protocol differs from the mSQL protocol are listed below:

25.1.3 MySQL と mSQL 2.0 間の SQL 構文の違いは?

Column types

他に加え次の項目オプション型を持ちます (among others; 「7.7 CREATE TABLE構文」節):
MySQL also supports the following additional type attributes:
mSQL column types correspond to the MySQL types shown below:
mSQL type Corresponding MySQL type
CHAR(len) CHAR(len)
TEXT(len) TEXT(len). len is the maximal length. And LIKE works.
INT INT. With many more options!
REAL REAL. Or FLOAT. Both 4- and 8-byte versions are available.
DATE DATE. Uses ANSI SQL format rather than mSQL's own format.
MONEY DECIMAL(12,2). A fixed-point value with two decimals.

Index Creation

Indexes may be specified at table creation time with the CREATE TABLE statement.
Indexes must be created after the table has been created, with separate CREATE INDEX statements.

To Insert a Unique Identifier into a Table

Use AUTO_INCREMENT as a column type specifier. 「 mysql_insert_id()」節参照.
Create a SEQUENCE on a table and select the _seq column.

To Obtain a Unique Identifier for a Row

Add a PRIMARY KEY or UNIQUE key to the table and use this. New in Version 3.23.11: If the PRIMARY or UNIQUE key consists of only one column and this is of type integer, one can also refer to it as _rowid.
Use the _rowid column. Observe that _rowid may change over time depending on many factors.

To Get the Time a Column Was Last Modified

Add a TIMESTAMP column to the table. This column is automatically set to the current date and time for INSERT or UPDATE statements if you don't give the column a value or if you give it a NULL value.
Use the _timestamp column.

NULL Value Comparisons

MySQL follows ANSI SQL, and a comparison with NULL is always NULL.
In mSQL, NULL = NULL is TRUE. You must change =NULL to IS NULL and <>NULL to IS NOT NULL when porting old code from mSQL to MySQL.

String Comparisons

Normally, string comparisons are performed in case-independent fashion with the sort order determined by the current character set (ISO-8859-1 Latin1 by default). If you don't like this, declare your columns with the BINARY attribute, which causes comparisons to be done according to the ASCII order used on the MySQL server host.
All string comparisons are performed in case-sensitive fashion with sorting in ASCII order.

Case-insensitive Searching

LIKE is a case-insensitive or case-sensitive operator, depending on the columns involved. If possible, MySQL uses indexes if the LIKE argument doesn't start with a wild-card character.

Handling of Trailing Spaces

Strips all spaces at the end of CHAR and VARCHAR columns. Use a TEXT column if this behavior is not desired.
Retains trailing space.

WHERE Clauses

MySQL correctly prioritizes everything (AND is evaluated before OR). To get mSQL behavior in MySQL, use parentheses (as shown in an example below).
Evaluates everything from left to right. This means that some logical calculations with more than three arguments cannot be expressed in any way. It also means you must change some queries when you upgrade to MySQL. You do this easily by adding parentheses. Suppose you have the following mSQL query:
mysql> SELECT * FROM table WHERE a=1 AND b=2 OR a=3 AND b=4;
To make MySQL evaluate this the way that mSQL would, you must add parentheses:
mysql> SELECT * FROM table WHERE (a=1 AND (b=2 OR (a=3 AND (b=4))));

Access Control

Has tables to store grant (permission) options per user, host, and database. 「6.8 特権システムはどのように動くか?」節参照.
Has a file `mSQL.acl' in which you can grant read/write privileges for users.

25.2 How MySQL compares to PostgreSQL

We would first like to note that PostgreSQL and MySQL are both widely used products, but their design goals are completely different. This means that for some applications MySQL is more suitable and for others PostgreSQL is more suitable. When choosing which database to use, you should first check if the database's feature set is good enough to satisfy your application. If you need speed, MySQL is probably your best choice. If you need some of the extra features that PostgreSQL can offer, you should use PostgreSQL.

PostgreSQL はさらに進んだ機能をいくつか持っています。ユーザ定義型、トリガ、 ルール、トランザクションのような。 (currently it has about the same semantics as MySQL's transactions in that the transaction is not 100% atomic). しかし ANSI SQL と ODBC の標準型と 機能の多くが欠けています。サポート/未サポートの型/関数の完全な一覧は crash-me Web page を見てください。

通常、PostgreSQLMySQL よりとても遅いです。 「13.7 Using Your Own Benchmarks」節参照. This is due largely to the fact that they have only transaction-safe tables and that their transactions system is not as sophisticated as Berkeley DB's. In MySQL you can decide per table if you want the table to be fast or take the speed penalty of making it transaction safe.

The most important things that PostgreSQL supports that MySQL doesn't yet support:

Sub select
Foreign keys
Stored procedures
An extendable type system.
A way to extend the SQL to handle new key types (like R-trees)

MySQL, on the other hand, supports many ANSI SQL constructs that PostgreSQL doesn't support. Most of these can be found at the crash-me Web page.

If you really need the rich type system PostgreSQL offers and you can afford the speed penalty of having to do everything transaction safe, you should take a look at PostgreSQL.

Go to the first, previous, next, last section, table of contents.