Re: Need concrete "Why Postgres not MySQL" bullet list - Mailing list pgsql-advocacy
From | Ian Barwick |
---|---|
Subject | Re: Need concrete "Why Postgres not MySQL" bullet list |
Date | |
Msg-id | 200308210022.29042.barwick@gmx.net Whole thread Raw |
In response to | Need concrete "Why Postgres not MySQL" bullet list (Josh Berkus <josh@agliodbs.com>) |
Responses |
Re: Need concrete "Why Postgres not MySQL" bullet
(Anastasios Hatzis <ahatzis@ithcorp.com>)
Re: Need concrete "Why Postgres not MySQL" bullet list ("Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>) |
List | pgsql-advocacy |
On Wednesday 20 August 2003 17:39, Josh Berkus wrote: > Folks, > > I need someone to prepare a standard response for me to send out to > inquiries on this topic. I get them a lot. > > What I'd like is a factual, non-perjorative list of the things which > PostgreSQL and the PostgreSQL project have that MySQL does not, Oh dear, this excludes my usual one-item "things to think about when considering what database to use": * PROPER USAGE OF NULL mysql> select * from ai_test where id is null; +----+-------+ | id | txt | +----+-------+ | 1 | hello | +----+-------+ 1 row in set (0.00 sec) ;-). I digress. Off the top of my head, in no particular order: * VIEWS VIEWs are an important element of efficient professional database design, pushing repetitive, often duplicated queries into the backend, increasing the flexibility of an application and reducing the risk of errors. They also enable the encapsulation of table structure, which may change with application development, behind a consistent interface. - MySQL has no support for views, but promises them in a future version, for which no target production date exists: http://www.mysql.com/doc/en/ANSI_diff_Views.html * TRIGGERS Triggers enable predefined actions to be carried out before or after inserts, deletes and updates are performed on tables. This enables automation of many tasks in the database, for example logging specific events such as changes to a table holding accounting data, or checking and modifying a column prior to insertion or update. - MySQL does not support triggers. Implementation is planned: http://www.mysql.com/doc/en/ANSI_diff_Triggers.html * SEQUENCES Often it is useful or necessary to generate a unique number for a row of data, for example for providing a primary key ID. Many databases including PostgreSQL provide sequences - functions for automatically generating unique numbers in predefined increments. - MySQL provides only a very primitive sequence generator, AUTO_INCREMENT, whose behaviour is difficult to modify. It is not possible to explicitly set the current value of a specific AUTO_INCREMENT sequence or an incrementation other than 1. - AUTO_INCREMENT is implemented as SELECT MAX(col) FROM table (see: http://www.mysql.com/doc/en/InnoDB_auto-increment_column.html ) which makes concurrent transactions prone to deadlocks - The use of the NULL value to provoke insertion of the next sequence value means it is impossible to have a sequence on a NULLable column in MySQL. - there can be only one AUTO_INCREMENT column per table * RULES, TYPES, DOMAINS * PROCEDURES in a variety of languages... * DDL - Data definition language (table creation statements etc.) in MySQL are not transaction based and cannot be rolled back. - Some parts of a table definition, although syntactically corrent, maybe ignored without warning. This applies particularly to syntax such as CREATE TABLE my_table ( id INT NOT NULL PRIMARY KEY, fkey_id INT NOT NULL REFERENCES other_table(id) ) TYPE=InnoDB See: http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html - MySQL may, at its discretion, silently alter column specifications. See: http://www.mysql.com/doc/en/Silent_column_changes.html - MySQL only permits constants as default values not functions or expressions. See: http://www.mysql.com/doc/en/CREATE_TABLE.html This makes it impossible to provide default values like this: CREATE TABLE deftest ( id INT, date_at_creation TEXT DEFAULT 'The date is '|| to_char('dd-mm-yyyy', now()) ); * GENUINE REFERENTIAL INTEGRITY An essential part of the relational model is the ability to create foreign keys, which define relationships between tables. Naturally only values which exist in the referenced table can be used in a foreign key column. PostgreSQL has provided integrated foreign key support since (find out when). - In MySQL foreign keys are an "optional extra" and are only available when the InnoDB table type is specified. - Not all MySQL server installations are configured to provide InnoDB support, e.g. ISP-run servers. - despite the implementation of foreign keys with InnoDB tables, MySQL does not provide robust referential integrity. Under certain circumstances MySQL will insert into or permit the presence of values in a foreign key which are not present in the table referred to. For example, if a foreign key column can contain null values, and the column's definition is changed to NOT NULL, MySQL will insert 0 (zero) into the previously NULL columns, even if the column referred to in a different table does not contain 0. - No ON UPDATE ... support * RIGOROUS FAILURE TESTING When developing a database, and in day-to-day operation, it is essential that erroneous statements fail with a specific warning to prevent violations of data integrity. - in many cases where a statement would be expected to fail, MySQL will silently insert default values. For example, when (mistakenly) inserting an integer 1 into an ENUM field, MySQL will insert a string containing '0' rather than raise an error. * DICTIONARY BASED FULL-TEXT INDEXING Although SQL provides several methods of searching for patterns in text fields (LIKE, regexes), these do not provide sufficient functionality for more complex searches, e.g. as used by search engines. - MySQL provides a builtin index type FULLTEXT, which allows keyword searches. In contrast to PostgreSQL's tsearch2 functionality, this does not provide for advanced natural language based searches using features such as dictionary lookups and stemming. * LICENSING - MySQL is available as "open source", but depending on useage not always with an open source license. MySQL's licencing conditions are subject to change. - PostgreSQL is available under a BSD-style licence to all users whether commercial or private. I can take the above and any further additions and flesh it out into a more readable list (if noone else wants to). I have a project at the moment which will probably be marketed as a MySQL-compatible application ("it's what the users have"), though I am prototyping it in PostgreSQL, and I'm collecting a lot of interesting insights... Ian Barwick barwick@gmx.net
pgsql-advocacy by date: