0

I want to do something that sounds so easy, but I was not able to find an easy way to solve it:
Create a new table with, let's say, 500000 rows and get them numbered automatically (ID).

Using PHP it's probably a simple loop, but I wonder if it is really necessary to use PHP.

I am almost sure there is a simple solution using a MySQL command or even with phpMyAdmin.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
user5564160
  • 57
  • 7
  • 1
    If you make that column an auto-numbering column, you just have to insert the 50000 rows and it numbers automatically. Using an editor like Notepad++, you can easily generate an insert statement that inserts 500000 rows, and the auto-numbering of the field takes care of the rest. If you're stuck with any of these, please ask a more specific question. – GolezTrol Nov 15 '15 at 12:29
  • 1
    seems a little extreme @GolezTrol. Where do you draw the line? I'd draw it at like 1 row. Here is how I would do 4.7M rows, takes 1 minute: http://stackoverflow.com/a/33666394 – Drew Nov 15 '15 at 23:36
  • I doubt if mine is extreme, but yours is certainly clever. But then again. I think I've never had to insert half a million empty lines in a table in years that I can remember. And even if I did have to do that one, and one time only, I wouldn't worry too much about the efficiency of this process too much. – GolezTrol Nov 16 '15 at 07:08

2 Answers2

5

Yes, you can use only mysql

CREATE PROCEDURE insertProcedure()
BEGIN
    DECLARE i int DEFAULT 0;
    WHILE i <= 500000 DO
        INSERT INTO table_name (id, col1, col2) VALUES (i, null, null);
        SET i = i + 1;
    END WHILE;
END

Then cal it like:

CALL insertProcedure();

Of course, you need to adapt the insert line to your case (table_name, number of columns, values).

If id is set to autoincrement, then you can replace the first i with null and let mysql use it's calculated value for it.

Note: to test it, use a much lower limit (say 500). Inserting 0.5 million rows will take some time, especially on a normal PC

Alex Tartan
  • 6,736
  • 10
  • 34
  • 45
  • Sorry, I didn't manage to store the procedure, I always get #1064 - You have an error in your SQL syntax. I put it in the command line of my phpMyAdmin, right? My version is 5.6.24.... – user5564160 Nov 15 '15 at 20:17
  • 1
    I think this would insert 500001 records. – GolezTrol Nov 16 '15 at 07:10
2

Join any two tables without condition and the resulting number of rows will be multiplied. Increase the id on every select from that result using a local variable.

This creates about 1M rows (2^20);

set @i = 0;
drop TEMPORARY table if exists dummyids;
create TEMPORARY table dummyids
    select @i := @i + 1 as id
    from (select true union all select true) t0
    join (select true union all select true) t1
    join (select true union all select true) t2
    join (select true union all select true) t3
    join (select true union all select true) t4
    join (select true union all select true) t5
    join (select true union all select true) t6
    join (select true union all select true) t7
    join (select true union all select true) t8
    join (select true union all select true) t9
    join (select true union all select true) t10
    join (select true union all select true) t11
    join (select true union all select true) t12
    join (select true union all select true) t13
    join (select true union all select true) t14
    join (select true union all select true) t15
    join (select true union all select true) t16
    join (select true union all select true) t17
    join (select true union all select true) t18
    join (select true union all select true) t19
;
select * from dummyids;
Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53