{"id":1148,"date":"2009-09-01T08:40:24","date_gmt":"2009-09-01T06:40:24","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1148"},"modified":"2009-09-01T08:45:06","modified_gmt":"2009-09-01T06:45:06","slug":"generating-numbers-out-of-seemingly-thin-air","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/generating-numbers-out-of-seemingly-thin-air","title":{"rendered":"Generating numbers out of seemingly thin air"},"content":{"rendered":"

In some of my previous posts I’ve used a numbers<\/strong> table, like one holding values 1, 2, 3, …, 255<\/strong>. Such table can be used for string walking, joining with other tables, performing iterations.<\/p>\n

The existence of number tables has always been a little pain. Yes, they’re very, very<\/em> simple, but they need to be there<\/em>. So if you just need to script some SQL query, you may find that you need to create such tables. Ummm… this means you need to have privileges (at least CREATE TEMPORARY<\/strong> and INSERT<\/strong>, if not CREATE<\/strong>).<\/p>\n

The other day, Baron Schwartz posted How to round to the nearest whole multiple or fraction in SQL<\/a>. In an offhand way, he generated some random numbers using the mysql.help_topic<\/strong> table. I then realized that post solved something I’ve been looking for: using a sure-to-exist table on any MySQL installation.<\/p>\n

What does the table consist of? It consists, among other columns, an incrementing help_topic_id<\/strong> column:<\/p>\n

\n
SELECT help_topic_id FROM mysql.help_topic LIMIT 10;\r\n+---------------+\r\n| help_topic_id |\r\n+---------------+\r\n|             0 |\r\n|             1 |\r\n|             2 |\r\n|             3 |\r\n|             4 |\r\n|             5 |\r\n|             6 |\r\n|             7 |\r\n|             8 |\r\n|             9 |\r\n+---------------+<\/pre>\n<\/blockquote>\n
Still feels unsafe?<\/h4>\n
The above result provides with sequential integers. But can we guarantee this? Will the numbers never have skipped values? We don’t have to rely on these values. We can force them to our liking:<\/p>\n
\n
SELECT @counter := @counter+1 AS value\r\nFROM mysql.help_topic, (SELECT @counter := 0) AS sel1\r\nLIMIT 10;\r\n+-------+\r\n| value |\r\n+-------+\r\n|     1 |\r\n|     2 |\r\n|     3 |\r\n|     4 |\r\n|     5 |\r\n|     6 |\r\n|     7 |\r\n|     8 |\r\n|     9 |\r\n|    10 |\r\n+-------+<\/pre>\n<\/blockquote>\n
All we actually need is the existence of rows within this table. We don’t care which columns, what their names are, and of which data types they are. Said table currently has 484 rows. One can use CROSS JOIN<\/strong> to achieve more than that:<\/p>\n
\n
SELECT @counter := @counter+1 AS value\r\nFROM mysql.help_topic t1, mysql.help_topic t2, (SELECT @counter := 0) AS sel1\r\nLIMIT 20000;\r\n+-------+\r\n| value |\r\n+-------+\r\n|     1 |\r\n|     2 |\r\n|     3 |\r\n|     4 |\r\n|     5 |\r\n...\r\n| 19992 |\r\n| 19993 |\r\n| 19994 |\r\n| 19995 |\r\n| 19996 |\r\n| 19997 |\r\n| 19998 |\r\n| 19999 |\r\n| 20000 |\r\n+-------+<\/pre>\n<\/blockquote>\n
Number generation<\/h4>\n
We are now in full control of generated numbers. We don’t have to generate sequential numbers. We can generate odd numbers only; multiples of 10, of PI… Following I’ll be generating the Fibonacci series:<\/p>\n
\n
SELECT @c3 := @c1 + @c2 AS value, @c1 := @c2, @c2 := @c3\r\nFROM mysql.help_topic, (SELECT @c1 := 1, @c2 := 0) sel1\r\nLIMIT 15;\r\n+-------+------------+------------+\r\n| value | @c1 := @c2 | @c2 := @c3 |\r\n+-------+------------+------------+\r\n|     1 |          0 |          1 |\r\n|     1 |          1 |          1 |\r\n|     2 |          1 |          2 |\r\n|     3 |          2 |          3 |\r\n|     5 |          3 |          5 |\r\n|     8 |          5 |          8 |\r\n|    13 |          8 |         13 |\r\n|    21 |         13 |         21 |\r\n|    34 |         21 |         34 |\r\n|    55 |         34 |         55 |\r\n|    89 |         55 |         89 |\r\n|   144 |         89 |        144 |\r\n|   233 |        144 |        233 |\r\n|   377 |        233 |        377 |\r\n|   610 |        377 |        610 |\r\n+-------+------------+------------+<\/pre>\n<\/blockquote>\n
Conclusion<\/h4>\n
Using 5.0 and above, you can also use the various INFORMATION_SCHEMA<\/strong> tables (e.g. INFORMATION_SCHEMA.COLLATIONS<\/strong>). Some of these may be slow to load, though.<\/p>\n
When you can (and need), have a prepared numbers table. When unable to create one, you can generate such numbers using tables which are certain to exist (at least until the next major version).<\/p>\n","protected":false},"excerpt":{"rendered":"
In some of my previous posts I’ve used a numbers table, like one holding values 1, 2, 3, …, 255. Such table can be used for string walking, joining with other tables, performing iterations. The existence of number tables has always been a little pain. Yes, they’re very, very simple, but they need to be […]<\/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":[21],"class_list":["post-1148","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-iw","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1148","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=1148"}],"version-history":[{"count":19,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1148\/revisions"}],"predecessor-version":[{"id":1243,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1148\/revisions\/1243"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}