world database<\/a> for demonstration.<\/p>\nGROUP_CONCAT<\/h4>\n
Assume the following query: SELECT CountryCode, COUNT(*) FROM City GROUP BY CountryCode<\/code><\/strong>, which selects the number of cities per country, using MySQL’s world database. It is possible to get a name for one “sample” city per country using standard SQL: SELECT CountryCode, Name, COUNT(*) FROM City GROUP BY CountryCode<\/code><\/strong><\/p>\nBut in MySQL it is also possible to get the list of cities per group: SELECT CountryCode, GROUP_CONCAT(Name), COUNT(*) FROM City GROUP BY CountryCode<\/code><\/strong>. This will provide with a comma delimited string of all city names per country.<\/p>\nORDER BY NULL<\/h4>\n
If you ran the previous queries, you may have noticed that the results were ordered by CountryCode. MySQL’s default behavior when GROUP BY<\/code><\/strong> is used, is to order by the grouped column. But this means sorting is required, possibly using merge passes and temporary tables. MySQL accepts the following syntax:<\/p>\nSELECT CountryCode, COUNT(*) FROM City GROUP BY CountryCode ORDER BY NULL<\/code><\/strong><\/p>\nIf you EXPLAIN<\/code><\/strong> the query, you’ll see no “Using filesort”. When not using ORDER BY NULL<\/code><\/strong>, “Using filesort” appears.<\/p>\nALTER TABLE … ORDER BY<\/h4>\n
MyISAM tables are not clustered. The table data is independent of indexes. Depending on concurrent_insert<\/code><\/strong> settings, new rows are either appended to the end of the table, or fill the space previously occupied by DELETE<\/strong>d rows.<\/p>\nWhen you SELECT (*) FROM Country<\/code><\/strong>, the order of rows is as stored on disk. It is possible to do a one-time reordering of rows in a MyISAM table by executing: ALTER TABLE Country ORDER BY Code<\/code><\/strong>. This is a lengthy operation (on large tables), which locks the table, so take care when using it. The change does not last for long, either: as you INSERT<\/code><\/strong> new rows, the rows get out of order again. But if your table does not get modified, or only gets modified rarely, this is a nice trick to use when order of rows is important, and you don’t want to pay the price of sorting per query.<\/p>\nROW_COUNT()<\/h4>\n
Anyone who uses MySQL with a connector (say, Connector\/J with JDBC), knows that INSERT<\/code><\/strong>, DELETE<\/strong> <\/code>and UPDATE<\/strong> <\/code>statements return with an integer value: the number of modified rows. In MySQL, the explicit way to get the number of modified rows is to invoke SELECT ROW_COUNT()<\/code><\/strong> right after your query. This method is useful if you like to know whether your DELETE<\/code><\/strong> did in fact remove rows, or INSERT IGNORE<\/code><\/strong> did in fact add a row, etc.<\/p>\nLIMIT<\/h4>\n
Well, MySQL DBAs are familiar with it. I just thought I’d mention LIMIT<\/code><\/strong>, since it’s a MySQL deviation. I was surprised to find that out, when an Oracle DBA once asked me how I did paging with results. “You mean like LIMIT 60,10<\/code><\/strong>?” I asked, and he replied: “LIMIT??”. So, you can LIMIT<\/code><\/strong> to limit the number of results, like: SELECT * FROM Country LIMIT 10<\/code><\/strong>, to only get first 10 rows, or to do paging like: SELECT * FROM Country LIMIT 60,10<\/code><\/strong>, which skips 60 rows, then reads 10.<\/p>\nSQL_CALC_FOUND_ROWS, FOUND_ROWS()<\/h4>\n
While at it, it may be required to use LIMIT to only return 10 rows, but still ask MySQL how many rows there really were. Do it like this:<\/p>\n
SELECT SQL_CALC_FOUND_ROWS Code, Name FROM Country LIMIT 10;<\/code><\/strong><\/p>\nSELECT FOUND_ROWS();<\/code><\/strong><\/p>\nFirst query gives the required 10 results. Second query says “239”, which is the total rows I would get had I not used LIMIT<\/code><\/strong>. Note that a SELECT SQL_CALC_FOUND_ROWS<\/code><\/strong> is a “heavy” query, which actually searches through the entire rowset, and then only returns the LIMITed rows. Use with care.<\/p>\nPROCEDURE ANALYSE<\/h4>\n
A very nice diagnostic tool, which tells us what data types are proper based on existing data. If we have an INT<\/code><\/strong> column, but all values are smaller than 200, PROCEDURE_ANALYSE()<\/code><\/strong> recommends that we use a TINYINT<\/code><\/strong>. Usage: SELECT * FROM Country PROCEDURE ANALYSE(10,10)<\/code><\/strong>. Just remember it does not anticipate data growth. It only relies on current data.<\/p>\nINSERT IGNORE<\/h4>\n
OK, I said above that it is commonly used, but couldn’t help myself, it’s just too useful to leave out. INSERT IGNORE INTO City (id, Name) VALUES (1000, 'Te Anau')<\/code><\/strong> will silently abort if there’s a UNIQUE KEY<\/code><\/strong> on `id` and an existing id=1000 value. A normal INSERT<\/code><\/strong> will terminate with an error, or raise an Exception in your application’s code. It is of particular use when doing an extended INSERT: INSERT IGNORE INTO City (id, Name) VALUES (1000, 'Te Anau'), (9009, 'Wanaka')<\/code><\/strong> may have trouble with the first row, but will<\/em> insert the second row. ROW_COUNT()<\/code><\/strong> can tell me how well it went.<\/p>\n","protected":false},"excerpt":{"rendered":"“Standard SQL” is something you read about. All popular databases have modified version of SQL. Each database adds its own flavor and features to the standard. MySQL is no different.<\/p>\n
Some deviations are storage engine dependent. Others are more general. Many, such as INSERT IGNORE, are commonly used. Here’s a list of some MySQL deviations to SQL, which are not so well known.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[13,21,20],"class_list":["post-126","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-myisam","tag-sql","tag-syntax"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-22","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/126","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=126"}],"version-history":[{"count":25,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/126\/revisions"}],"predecessor-version":[{"id":370,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/126\/revisions\/370"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=126"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=126"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=126"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}