The difference between MySQL and access

To adapt to the ever-changing technology, a considerable number of software engineers are gradually moving from desktop software such as Microsoft Access and SQL Server to using MySQL. While MySQL is strictly not an opponent of SQL Server, many service providers support MySQL as a cheap and efficient alternative.

Susan Sales Harkins, who often publishes articles in CNET builder.com, is an expert in Microsoft Office. She is also the author of using Microsoft Access 97 and using Microsoft Access 20,002, both of which are published by Que.

Martin W. Reid is also a frequent author of CNET Builder.com, an analyst and programmer at Queen's University of Belfast (Queen's University). He also directs the course of relational design, and also serves as a consultant for small businesses in Northern Ireland in addition to work.

¡ø Consider the reasons for using MySQL

If you are looking for reliable software to support your site development, the following reasons explain why you should consider MySQL instead of the other:

• It is cheap (usually free).

• Its network load is relatively few.

• It has been well optimized (highly optimized).

• It is simpler for the application to make backups through it.

• It provides a flexible extension interface (ODBC) for various data formats.

• It is better to learn, and easy to operate.

• You can afford a customer support fee.

▲ questions about "$"

Simply put, you won't find a cheaper one than MySQL. In fact, for most users, MySQL is free. Sometimes, although a small amount of authorization fee is required, this payment rule is limited to the following two situations:

• Use MySQL in an embedded (embedded) way

• Use MySQL only for business purpose software

For example, the Windows version of MySQL requires authorization. MySQL is much cheaper than any other software, though it costs only a little more than the dollar. The retail price of Office XP Developer is USD $799, and the upgraded version is USD $549. Access 2002 of the price is USD $339 Yuan, upgraded version is the U.S. dollar $109 yuan.

▲ Avoid clogging

Access is not a MySQL competitor to the need for multiple users to read and write information together. Access loses when it is connected to about 15 users. We've also heard that when only five people are connected, there are some problems (this is not to say that only five people can connect to a Web site supported by Access at the same time). "Simultaneous linking" (simultaneous connection) is in fact a concurrent process (concurrent process). Therefore, although the number of links that Access can handle is unrestricted, it does not matter as long as those links remain within the scope limit of concurrent processing. For read-only sites (these sites are not the few you think) it can support up to 255 users. and larger web sites, it is inevitable that you must upgrade to SQL Server to improve stability and efficiency.

In contrast, the MySQL default maximum number of connections is 100 users. However, we must not judge its effectiveness by the internal construction of a program. So far, we have not heard any complaints from users of the larger and frequently accessed websites using MySQL. In addition, even if there is a large amount of information on the network, it does not seem to be the MySQL Query Optimization (optimization) caused much impact.

With the same hardware and data dimensions on Windows 98, MySQL behaves faster than Access 2000-but not in all cases. The two have a significant difference in the effectiveness of the data update, the same data update, Access to spend twice times the time. If you're dealing with a small amount of data on a high-speed system, you won't notice the difference between the two. But the difference in effectiveness is evident only when hundreds of thousands of of the data are processed. MySQL loses to Access only when it is working on object structure. When you create a table and an index, MySqL locks the table, which can cause a lot of data processing to slow down. However, the last issue mentioned above is usually not a problem when the website is developed. Because on the website, what we pay attention to is the speed of the query when the user visits, not the data storage structure itself. So, in this field, MySQL wins.

Other advantages of ▲mysql

• optimization

For MySQL optimization, we can say that the main problem is your hardware conditions, not MySQL itself. But for Access, (and other desktop software) things don't work that way. Yes, Microsoft Jet Database is really efficient, but it's not the fastest. If your design is very poor, your site will still be affected and slowed down. The structure design also affects MySQL, for example, MySQL does not support foreign keys (foreign key). This shortcoming will affect your design and the efficiency of the site. For Web sites that use MySQL, you should be aware of how to minimize access Io, how to keep one or more CPUs at a high speed, and the appropriate network bandwidth, rather than the actual design and data query statements. In fact, some Web developers are calling MySQL the fastest runner in the market today. However, MySQL does not run well when you have a lot of forms that need to be updated within a transaction process (transaction) at the same time.

• Backup

If you've had the painful experience of rescuing a corrupted MDB file, you'll be very appreciative of MySQL. This is another place in MySQL that trumps Access. First, mysqldump will produce a much better and more reliable backup file than Access. In Access, by contrast, you simply copy an MDB file to make a backup. Second, even if the MySQL backup is partially damaged, it is much easier to recover than a corrupted MDB file.

• Extensibility (scalability) and data processing capabilities

In the words of a mountaineer, Access is compared to MySQL by comparing it with the Pike ' s Peak in the Colorado Rocky Mountains. The truth is this simple: MySQL can handle files that are much larger than Access can handle. If you're trying to get Access to a 100MB MDB file, you have to prepare a thick dictionary of records that have been recorded from customers complaining about inefficient websites. Similar to running on MySQL, there is no sign of heavy load.

In addition, MySQL provides a high degree of diversity that provides a wide variety of user interfaces, including command-line client operations, Web browsers, and a wide variety of program language interfaces, such as c+,perl,java,php, and Python. You can use pre-packaged clients, or simply write a suitable application yourself. MySQL can be used for unix,windows, as well as OS/2 platforms, so it can be used on PCs or on a PC.

Yes, Microsoft ActiveX data Objects Library (ADO) does make Access more flexible in the external data market (foreign data market). It allows you to remove data from the location of the data and then display the data on the common interface (that is, a Web browser). The downside, though, is that ADO is clunky (it's a hodgepodge of resources) and it takes a lot of money and time to learn it, even if you're a good development engineer or a software engineer. No one can learn the ADO in one day.

▲ Learning curve

If you are already familiar with technology, then basically you have no problem. A proficient person can take the MySQL society and add this experience to his resume within a day. In contrast, Access is a much more complex and development tool. Even a good development engineer takes a while to have enough expertise to use the software effectively.

As you would expect, MySQL supports Structured Query Language (structured query Language, SQL). If you've learned some version of the SQL language, things will be much better. Development engineers with VB or VBA knowledge will find that their previous ASP backgrounds can help them shorten their learning time.

▲ Customer Support

While easy and free customer support is no longer available, MySQL offers a list of electronic groups for your reference. Some are technical, and members often provide the best customer support for each other-they share experience and expertise. In addition, you can also purchase a customer-supported version, including email support or phone support. Generally speaking, the customer support rate is not fixed, so we can not provide you with the relevant price information.

The deficiencies of the ▲mysql

Access is a relational management system (RDBMS), but MySQL is not at every level. This means that while MySQL is good, it is not the best. The following list records the current relevance level and management level that is not supported by MySQL:

MySQL cannot handle complex relevancy functions, such as subqueries (subqueries), although most subqueries can be written as joins. We look forward to the next release, this feature will be added.

Another feature that MySQL does not provide support for IS transaction processing (transaction) and transaction submission (commit)/revocation (rollback). A transaction is a group or set of commands that are executed collectively as a unit. If a transaction cannot be completed, then none of the instructions in the entire transaction are actually executed. It's really disappointing that MySQL doesn't support this feature for commercial websites that must handle online orders. But can use Maxsql, a separate, it can through the plug-in table to support the transaction function.

The foreign key (foreign key) and referential integrity restrictions (referential integrity) allow you to make constraints between the data in the form and add the constraint (constraint) to the information you specify. The functionality that MySQL does not represent an application that relies on complex data relationships is not suitable for use with MySQL. When we say MySQL does not support foreign keys, we are referring to referential integrity restrictions-MySQL does not support the rules of foreign keys, of course, there is no support for the chain delete (cascading delete) function. In short, if your job requires complex data connections, you should use the original Access bar.

You will not find the stored processes (stored procedure) and triggers (trigger) in MySQL either. (For these features, Access provides an event procedure for the relative events process.) )

Access's GetRows features provide better data pickup.

▲ Summary

The following table gives you a basic idea of how mysql,access, and SQL Server, generally compare:

-Visit Frequent websites

· Mysql√

· access√**

· SQL server√

-Complex Data Association

· Mysqlx

· Access√

· SQL server√

-Online order Processing

· mysql√*

· access√***

· SQL server√

-Compatibility

· Mysqlx

· access√****

· SQL server√

-Easy to use and operate

· Mysql√

· Accessx

· SQL Serverx

Note:

* Need Maxsql

* * If the information is read-only

Additional functionality obtained through Jet SQL

Because only ADO

If you need to use complex and have a lot of resources and money, then you can use SQL Server. If you still need complex but no strong backup, then use Access to see. As for other people, you should at least give MySQL a chance to use it!