31.2. INFORMATION_SCHEMA Versus SHOW For the most part, the INFORMATION_SCHEMA database and SHOW statements provide access to similar kinds of metadata. However, sometimes one source of information may be preferable to the other. This section compares them for the purpose of highlighting each one's strengths and weaknesses. Advantages of INFORMATION_SCHEMA over SHOW: INFORMATION_SCHEMA is a feature of standard SQL, whereas SHOW is a MySQL-specific statement. This means that INFORMATION_SCHEMA is more portable. You're likely to have an easier time porting applications for use with other database systems if they use INFORMATION_SCHEMA than if they use SHOW statements. With INFORMATION_SCHEMA, you always use SELECT syntax to obtain metadata, regardless of the type of information in which you're interested. SHOW involves a different statement for each type of metadata, and they don't all have the same syntax. With SELECT and INFORMATION_SCHEMA, you have complete flexibility to choose what to retrieve. You can name which columns to select, apply arbitrary conditions for restricting which rows to retrieve, and sort the result. SHOW is not so versatile. Some forms of SHOW support a LIKE clause to restrict which rows to display, and MySQL 5 adds a WHERE clause as a more flexible way to restrict the rows. But in either case, the rows returned are in a fixed order. They also consist of a fixed set of columns. You cannot omit columns in which you're not interested. Because the information in INFORMATION_SCHEMA can be retrieved with all the flexibility of SELECT, you can use joins, unions, and subqueries. You cannot do the same with SHOW statements. By using CREATE TABLE … SELECT or INSERT … SELECT, the contents of INFORMATION_SCHEMA can be retrieved and stored into another table for use in subsequent statements. The information produced by SHOW can be retrieved for display only. It cannot be stored in another table.
Advantages of SHOW over INFORMATION_SCHEMA: SHOW is available for releases of MySQL older than MySQL 5. SHOW is often more concise. For example, the following two statements display the names of the tables in the world database, but the one that uses SHOW clearly is shorter: SHOW TABLES FROM world; SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world';
The brevity of SHOW can make it an easier statement to issue. This factor often tilts the balance toward SHOW, especially for interactive use when you're entering statements manually. A SHOW statement also can be easier to remember, compared to the corresponding SELECT that uses INFORMATION_SCHEMA.
|
