Middleware in the MySQL database Field

MySQL databaseIt is a semi-commercial database that is widely popular in the Linux community. MySQL can run on most Linux platforms (i386, iSCSI, etc), as well as a few non-Linux or even non-Unix platforms. MySQL database is a small relational database management system. Although it is not very powerful, it has won the favor of many users thanks to its own advantages.

1. license fee

The popularity of MySQL is largely due to its loose, except for a slightly unusual licensing fee. The price of MySQL varies with the platform and installation method. MySQL Windows versions (NT and 9X) are not free of charge under any circumstances, and any Unix variant (including Linux) mySQL is free of charge if it is installed by the user or the system administrator instead of a third party, and the third solution is required to pay the license fee.

2. Price

Platform Installation price

Windows NT, 9X any $200

Free installation for Unix or Linux

$200 for third-party installation in Unix or Linux

$200 for an application component

You can get a variety of support contracts with too much content to be listed. You can consult the MySQL site for the latest offer.

3. Installation

Most major software package formats (RPM, DBE, and TGZ) can be obtained on the MySQL site. The client library and various languages "Wrapper" can be obtained in separate RPM formats. The RPM format does not require much trouble and requires no initial configuration. The rc3.d (RedHat RPM is used as an example) generates an initial script. Therefore, the MySQL daemon is started to run when it is restarted in multi-user mode. MySQL daemon (mysqld) consumes a small amount of memory (on the Pentium 5.1 running RedHat 133, each daemon uses KB of memory and 4 MB of shared memory) mySQL can be loaded to the processor only when real queries are executed, which means that MySQL can be easily used for small databases without affecting other system functions.

4. Data Types

It is a good thing that fields support a large number of data types. Generally, integers, floating-point numbers, strings, and numbers are expressed in multiple lengths, and long BLOB (Binary Large OBject) types are supported. The automatic increment option can also be used to represent integer fields.

Unlike most other database systems, MySQL provides two relatively uncommon field types: ENUM and SET. ENUM is an enumeration type. It is very suitable for an enumeration type in Pascal language. It allows programmers to see field values similar to 'Red, 'green', and 'Blue, mySQL only stores these values as one byte. SET is also borrowed from Pascal. It is also an enumeration type, but a single field can store multiple values at a time, this ability to store multiple enumerated values may not give you some impression (and may threaten the definition of the third paradigm), but correct use of the SET and CONTAINS keywords can save a lot of table connections, it can achieve good performance improvement.

5. SQL compatibility

MySQL contains some different transformations from SQL standards. Most of them are designed as a compensation for the lack of SQL scripting language. However, other extensions make MySQL different. For example, LINK clause search automatically ignores case sensitivity. MySQL also allows users to customize SQL functions. In other words, a programmer can write a function and integrate it into MySQL. what it represents with any basic functions such as SUM () or AVG () there is no difference. The FUNCTION must be compiled into a shared library file (. so file) and loaded with a load function command.

It also lacks some common SQL functions and has no sub-selection (query in the query ). The View is gone. Of course, most subqueries can be rewritten using simple join clauses, but sometimes it is easier to think about problems with two nested queries than a large join. Similarly, the view only hides the where clause for programmers, but this is another convenience that programmers expect.

6. Stored Procedures and triggers

MySQL does not have a Stored Procedure language, which is the biggest restriction on programmers who are used to enterprise-level databases. Multi-statement SQL commands must be coordinated by the client code. In this case, multi-statement execution is allowed only by means of a sound Query Language and the ability to lock and unlock tables on the client.

7. Referential Integrity-RI)

One of the major defects of MySQL is the lack of a standard RI mechanism. However, MySQL creators do not ignore their users' desires and provide some solutions. One of them is the support for unique indexes. The lack of Rule restrictions (a fixed range limit on the given field) is compensated by a large number of data types. It does not simply provide check constraints (limits on the value of one field relative to another field in the same row), external keywords, and frequently RI-related "Cascading deletion" functions. Interestingly, when these functions are not supported, the SQL analyzer tolerates the syntax of these statements. This is intended to facilitate the migration of databases to MySQL. This is a good attempt, and it does leave a convenient way to support this function in the future. However, those who have not carefully read the document may mistakenly think that these functions actually exist.

7. Security

From beginning to end, my biggest complaint about MySQL was its security system. Its only drawback was its complexity and non-standard. In addition, it only changed when I called mysqladmin to re-read user permissions. Generally, SQL GRANT/REVOKE statements are only supported in the latest version, but at least they have them now. MySQL writers widely record their specific security systems, but it does need a learning process that may be useless.

8. backup and recovery, Data Import/Export

The lack of mandatory reference consistency significantly simplifies backup and recovery, and data import/export alone can be perfectly copied. The load data infile command provides great flexibility for DATA import. The select into command provides the data export equality function. In addition, since MySQL does not use the original partition, all database data can be backed up and saved in one file system. Database activities can be recorded. Unlike common database logs (storing changes or before/after recording images), MySQL records the actual SQL statements. This allows the database to be restored to the point before the failure, but does not allow commit and roll back operations.

9. Connectivity

MySQL client database is a C language database of the customer/Server structure. It means that one customer can query a database residing on another machine. However, MySQL's true strength lies in the language "wrapper" in this library. Perl, Pathon, and PHP are only part of it. The Apache Web server also has many modules, such as Directory Access files, which allow various Apache configuration information (such as Directory Access files) to use MySQL. The application interfaces are simple, consistent, and complete. In addition, the multi-platform ODBC driver is freely available.

10. Future

MySQL development continues to take effect quickly. In fact, the pace of development is a challenge for most open source code. Many new features are being addressed in the complaints mentioned in this Article. However, I will not evaluate the features that do not exist yet. The developers showed me that in future development, the query function will be added and the query speed will be improved as the highest priority.

11. Conclusion

Mysql is the middle school in the database field. It lacks most of the main features of a full-featured database, but has more features than the Xbase record storage engine. Like an enterprise-level RDBMS, it requires an active service provider daemon, but cannot consume resources as they do. The query language allows complex join queries, but all references must be guaranteed by the programmer.

MySQL finds a place in the Linux World-providing simplicity and speed while still providing enough functionality to make programmers happy. Database programmers will like their query functions and a wide range of customer libraries. Database administrators will find that the system lacks the primary database functions and they will find that it is applicable to simple databases (when purchasing big-name databases is not guaranteed) is valuable.

This article gives a comprehensive introduction to the MySQL database and analyzes the MySQL database from various aspects. I believe that through the above learning, I now have an updated understanding of the MySQL database, in the end, I hope you will gain some benefits from the content mentioned above.