Creating a Numbers Table in MySQL
You are missing semicolons, commas, and even after correcting syntax it is still not a good idea to select max from the table every time just to insert one more row in a loop.
Drop that and use generators from http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator :
CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15;
CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
FROM generator_16 lo, generator_16 hi;
CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
FROM generator_256 lo, generator_16 hi;
CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
FROM generator_256 lo, generator_256 hi;
CREATE OR REPLACE VIEW generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
FROM generator_64k lo, generator_16 hi;
And if for whatever reason you really need a table of numbers just do:
INSERT INTO numbers(number)
SELECT n FROM generator_64k WHERE n < 64000
MYSQL: Sequential Number Table
-- To use the bitwise solution you need a view of 2 to the power 25.
-- the following solution is derived from http://stackoverflow.com/questions/9751318/creating-a-numbers-table-in-mysql
-- the following solution ran in 43.8 seconds with the primary key, without it 4.56 seconds.
-- create a view that has 2 to the power 25 minus 1
-- 2 ^ 1
CREATE or replace VIEW `two_to_the_power_01_minus_1` AS select 0 AS `n` union all select 1 AS `1`;
-- 2 ^ 2
CREATE or replace VIEW `two_to_the_power_02_minus_1`
AS select
((`hi`.`n` << 1) | `lo`.`n`) AS `n`
from (`two_to_the_power_01_minus_1` `lo` join `two_to_the_power_01_minus_1` `hi`) ;
-- 2 ^ 4
CREATE or replace VIEW `two_to_the_power_04_minus_1`
AS select
((`hi`.`n` << 2 ) | `lo`.`n`) AS `n`
from (`two_to_the_power_02_minus_1` `lo` join `two_to_the_power_02_minus_1` `hi`) ;
-- 2 ^ 8
CREATE or replace VIEW `two_to_the_power_08_minus_1`
AS select
((`hi`.`n` << 4 ) | `lo`.`n`) AS `n`
from (`two_to_the_power_04_minus_1` `lo` join `two_to_the_power_04_minus_1` `hi`) ;
-- 2 ^ 12
CREATE or replace VIEW `two_to_the_power_12_minus_1`
AS select
((`hi`.`n` << 8 ) | `lo`.`n`) AS `n`
from (`two_to_the_power_08_minus_1` `lo` join `two_to_the_power_04_minus_1` `hi`) ;
-- 2 ^ 13
CREATE or replace VIEW `two_to_the_power_13_minus_1`
AS select
((`hi`.`n` << 1) | `lo`.`n`) AS `n`
from (`two_to_the_power_01_minus_1` `lo` join `two_to_the_power_12_minus_1` `hi`);
-- create a table to store the interim results for speed of retrieval
drop table if exists numbers_2_to_the_power_13_minus_1;
create table `numbers_2_to_the_power_13_minus_1` (
`i` int(11) unsigned
) ENGINE=myisam DEFAULT CHARSET=latin1 ;
-- faster 2 ^ 13
insert into numbers_2_to_the_power_13_minus_1( i )
select n from `two_to_the_power_13_minus_1` ;
-- faster 2 ^ 12
CREATE or replace view `numbers_2_to_the_power_12_minus_1`
AS select
`numbers_2_to_the_power_13_minus_1`.`i` AS `i`
from `numbers_2_to_the_power_13_minus_1`
where (`numbers_2_to_the_power_13_minus_1`.`i` < (1 << 12));
-- faster 2 ^ 25
CREATE or replace VIEW `numbers_2_to_the_power_25_minus_1`
AS select
((`hi`.`i` << 12) | `lo`.`i`) AS `i`
from (`numbers_2_to_the_power_12_minus_1` `lo` join `numbers_2_to_the_power_13_minus_1` `hi`);
-- create table for results
drop table if exists numbers ;
create table `numbers` (
`i` int(11) signed
, primary key(`i`)
) ENGINE=myisam DEFAULT CHARSET=latin1;
-- insert the numbers
insert into numbers(i)
select i from numbers_2_to_the_power_25_minus_1
where i <= 20000000 ;
drop view if exists numbers_2_to_the_power_25_minus_1 ;
drop view if exists numbers_2_to_the_power_12_minus_1 ;
drop table if exists numbers_2_to_the_power_13_minus_1 ;
drop view if exists two_to_the_power_13_minus_1 ;
drop view if exists two_to_the_power_12_minus_1 ;
drop view if exists two_to_the_power_08_minus_1 ;
drop view if exists two_to_the_power_04_minus_1 ;
drop view if exists two_to_the_power_02_minus_1 ;
drop view if exists two_to_the_power_01_minus_1 ;
Are you allowed to use numbers as table names in MySQL?
Rules for naming objects, including tables in MySql:
http://dev.mysql.com/doc/refman/5.1/en/identifiers.html
Identifiers may begin with a digit but
unless quoted may not consist solely
of digits.
So this would be invalid:
SELECT * FROM 12345;
But the following would be valid:
SELECT * FROM `12345`;
Or if running in ANSI mode the following would work:
SET @@session.sql_mode=ANSI_QUOTES;
SELECT * FROM "12345";
Generating a range of numbers in MySQL
If you need the records in a table and you want to avoid concurrency issues, here's how to do it.
First you create a table in which to store your records
CREATE TABLE `incr` (
`Id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Secondly create a stored procedure like this:
DELIMITER ;;
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
INSERT incr VALUES (NULL);
SET v1 = v1 - 1;
END WHILE;
END;;
DELIMITER ;
Lastly call the SP:
CALL dowhile();
SELECT * FROM incr;
Result
Id
1
2
3
4
5
How to insert numbers from 1 to 1000 to a table what i created in a procedure?
As per the docs, you can only use DECLARE
at given locations:
DECLARE
is permitted only inside aBEGIN
...END
compound
statement and must be at its start, before any other statements.Declarations must follow a certain order. Cursor declarations must
appear before handler declarations. Variable and condition
declarations must appear before cursor or handler declarations.
(You're also missing a space at numberINT
.)
Create a new table based on another two tables in MYSQL
You can use a CREATE ... SELECT
query to generate the user_summary
table. The SELECT
query counts how many purchases were made before or after each coupon for each user, as well as counting the total number of their coupons:
CREATE TABLE user_summary AS
SELECT u.user_id,
COALESCE(SUM(u.purchase_time < c.coupon_time), 0) AS purchase_before,
COALESCE(SUM(u.purchase_time >= c.coupon_time), 0) AS purchase_after,
COUNT(c.coupon_id) AS total
FROM user u
LEFT JOIN coupon c ON c.user_id = u.user_id
GROUP BY u.user_id
Output (of SELECT * FROM user_summary
) after running this query:
user_id purchase_before purchase_after total
Marta 1 1 2
Michael 0 0 0
Steve 0 1 1
Demo on db-fiddle
generate sequence of (telephone) numbers in table in mySQL
Create a table that contains the numbers from 0 to 9. Extract the starting and ending digits of each number, and join with the number table to find all the numbers in the range.
WITH digits AS (
SELECT 0 AS dig
UNION
SELECT 1 AS dig
...
UNION
SELECT 9 AS dig)
SELECT SUBSTR(phone1, 1, 5) + digits.dig
FROM yourTable
JOIN digits ON dig BETWEEN SUBSTR(phone1, 6, 1) AND SUBSTR(phone1, 8, 1)
WHERE phone1 != ''
This shows how to do it for the phone1
column, you can use UNION
to repeat it for the other 3 columns.
Related Topics
Delete Duplicate Records in SQL Server
How to Dump the Data of Some Sqlite3 Tables
Spark SQL Window Function With Complex Condition
Parse Comma-Separated String to Make in List of Strings in the Where Clause
SQL Server - How to Lock a Table Until a Stored Procedure Finishes
Select/Group by - Segments of Time (10 Seconds, 30 Seconds, etc)
Simple Random Samples from a SQL Database
How to Determine the Number of Days in a Month in SQL Server
Sqlite Database Default Time Value 'Now'
Efficient Way to Implement Paging
Group by Behavior When No Aggregate Functions Are Present in the Select Clause
Why Does MySQL Report a Syntax Error on Full Outer Join
How to Query a Tree Structure Table in MySQL in a Single Query, to Any Depth