-
MySQL has transformed from a "toy" in the hands of developers to "the most popular open source database in the world" today. The process has been accompanied by product version upgrades and the addition of some new features (especially enterprise database features). MySQL has transformed from a "toy" in the hands of developers to "the most popular open source database in the world" today. The process is accompanied by product version upgrades and the addition of some new features (especially enterprise database features). Now, with MySQL 5.0 being perfectly developed, few people call MySQL a "toy database" anymore. MySQL's rich functions meet the needs of many users. Oracle's recent actions show that they take MySQL very seriously - Oracle has expressed its intention to acquire MySQL several times.
MySQL product roadmap
Let's start with the more influential versions of MySQL and look at the updates of MySQL.
MySQL 4.0
MySQL 4.0 was released in March 2003, making new MySQL-based applications more widely available. But in version 4.0, MySQL does not support stored procedures, triggers, server-side pointers, or views. MySQL 4.0 was developed from 3.23. It has been greatly improved compared to the 3.23 version. It is mainly suitable for Web sites. At this time, MySQL is not yet an enterprise-level database.
The following are the main new features of MySQL 4.0:
FULLTEXT index: Perhaps the most anticipated thing for users is the FULLTEXT index.
FULLTEXT creates an index on a text field, providing a powerful and flexible mechanism for performing Boolean searches on the index. According to general development experience, developers usually have to create indexes and access text data, and FULLTEXT indexes are much better than imagined.
While many solutions are limited to full-word indexes, FULLTEXT indexes have no such limitation, allowing developers to add or split phrases.
ANSI SQL UNION: Supports the ANSI SQL UNION statement, which collects query results into a result set.
Multi-table operations: Multi-table UPDATE and DELETE can be performed.
New statements: Added some non-standard new statements familiar to other DBMS users (such as IDENTITY and TRUNCATE TABLE), as well as new functions such as FOUND_ROWS(), which can return the number of records that can be returned without a LIMIT clause.
InnoDB storage engine: The InnoDB storage engine was a standard feature of the server at the time and became an additional option in version 4.0. InnoDb is a table type that allows ACID-compliant transactions instead of the default MyISAM table type, which can speed up general use, but is not very useful for critical operations.
InnoDB tables use row-level locking, which means that updates to a record only lock that record, not the entire table. Locking the entire table is fairly fast when the selection accesses a large database (as is the case for most web sites), but slower when the number of inserts and updates approaches the number of options. Criticisms of MySQL have long focused on security and consistency issues with MyISAM tables, and ACID-compliant InnoDB tables go a long way in solving these problems.
Query caching: MySQL 4.0 can be faster in some cases. This is primarily accomplished through query caching, which stores duplicate query results to improve speed, although many mature applications perform their own query caching functionality at some code level. Certain statements have also been improved in speed.
Embedded Server: MySQL 4.0 comes with an Embedded Server library that allows applications to use MySQL as the underlying database.
latin1_de: MySQL 4.0 supports an additional character set latin1_de, which ensures that German words are stored correctly.
MyISAM: MyISAM tables in MySQL 4.0 currently support symbolic links at the table level, so Windows users can create symbolic links at the table level (this will always work for Unix users).
Security model: The security model of MySQL 4.0 has been enhanced, allowing administrators to authorize permissions in more granular ways. The new permissions allow users to create temporary tables, lock tables, perform certain replication tasks, view all existing databases, and even connect when the maximum connection limit is reached - very useful for DBAs to perform emergency tasks, and even allow running storage Procedure (this feature was implemented in MySQL 5). Relying on enhanced security mode, DBAs can also limit the number of connections, updates or queries users can make per hour.
MySQL 4 is designed to run on Novell Netware 6.0. In addition, many MySQL server variables can be changed without restarting the server, which is very useful since restarting will restore the old settings.
MySQL 4.1
After the launch of MySQL 4.1, for some users, 4.1 has more exciting upgrade possibilities than MySQL 4.0:
MySQL 4.1 supports subqueries
Many queries can be written more efficiently without using subqueries, but there are exceptions. Subqueries are a standard ANSI SQL feature.
Supports Unicode (UTF-8), allowing wider internationalization.
Each column, table, or database can have a different character set, which is necessary if data is stored in multiple languages.
Support geographic data (OpenGIS)
Enhanced warning sending. If one is not enough, MySQL 4.1 can send multiple warnings to the client, which is very useful for overall data processing.
Improved some speed. But these speed improvements may be offset by all the extra weight MySQL 4.1 takes on.
Even though the MySQL Manual is one of the best manuals released, MySQL 4.1 comes with a HELP command that only works with that version.
Derived tables are supported, such as:
SELECT table1.field1 FROM table, (SELECT * FROM table2) table3 WHERE table1.field1=table3.field1
Supports multi-row queries, allowing multiple queries to be run and then the final results read.
Various maintenance statements are stored in the binary log, allowing you to simplify maintenance tasks when replicating.
CREATE...LIKE allows developers to easily create new tables with the exact structure of existing tables.
In addition, three notable features of MySQL 4.1 include: stable OpenSSL support, more test preparation statements, and more testing of multiple character sets for a table.
MySQL 4.1 may be the first version of MySQL that actually "grows up." Due to some newly added features and functions in version 4.1 (such as geographical data, sub-select statements, derived tables), Oracle began to really pay attention to MySQL for the first time.
MySQL 5.0
Supports stored procedures. Stored procedures are the ANSI SQL standard most commonly used by developers in other database environments. For MySQL, this is long overdue. The syntax of stored procedures supported by MySQL 5.0 is similar to Oracle PL/SQL and T-SQL.
Trigger program (called a stored procedure when an event occurs)
Support pointers
True support for the VARCHAR data type, solving a long-standing MySQL VARCHAR bug.
Support for RTREE indexes in MyISAM tables will make accessing geographic data easy.
MySQL 5.1
Compared with version 5.0, MySQL 5.1 implements some new features:
Online backup (allows adding replication slaves without shutting down the master server).
The BIT type actually occupies 1 bit, not 1 character.
failsafe replication
-