How to generate random chars and insert with MySQL?

Question

Duplicate:
Inserting random characters to MYSQL Database

How can I generate 100 records with 5 random characters and insert into the database with a query.

I want to insert into this table:

codes
  id (auto-increment)
  codes

score 18 · Answer 1 · answered Nov 21 '11 at 07:22

Try this one -

SELECT CONCAT(
  CHAR( FLOOR(65 + (RAND() * 25))),
  CHAR( FLOOR(65 + (RAND() * 25))),
  CHAR( FLOOR(65 + (RAND() * 25))),
  CHAR( FLOOR(65 + (RAND() * 25))),
  CHAR( FLOOR(65 + (RAND() * 25)))
  ) random_string;

This query generates ASCII codes from 'A' to 'Z' and generates a random string from them. I cannot say that this way is elegant, but it works;-)

ScottJShea · Answer 2 · 2016-09-14T18:52:29.593

7

INSERT INTO codes_tbl (codes) VALUES (SUBSTRING(MD5(RAND()) FROM 1 FOR 5));

That should take care of it.

edited Sep 14 '16 at 18:52

answered Nov 21 '11 at 04:52

ScottJShea

7,041
11
44
67

4

Except that `md5` gives you a string that only includes hex digits so the codes will be quite limited. – mu is too short Nov 21 '11 at 05:05
1

hello, i cannot run the code. got an error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 – spotlightsnap Nov 21 '11 at 05:08
1

There is an issue with the parentheses:`INSERT INTO codes_tbl (codes) VALUES (SUBSTRING(MD5(RAND()) FROM 1 FOR 5));` – Nate Sep 14 '16 at 17:27

score 0 · Answer 3 · edited May 23 '17 at 12:09

0

In MySql U Can Do It Like This

insert into table ( SUBSTRING(MD5(RAND()) FROM 1 FOR 10) , field2 , field3) , ( SUBSTRING(MD5(RAND()) FROM 1 FOR 10) , field2 , field3) , .........

If You Want to Do it With Php. U Can Check This Link

Further You can Check The Following Questions Already asked at Stackoverflow

1. Random Number- MySql

2. Mysql insert random unique 8 chars

edited May 23 '17 at 12:09

Community

1
1

answered Nov 21 '11 at 05:07

ScoRpion

11,364
24
66
89

score 0 · Answer 4 · answered Nov 21 '11 at 07:50

Could you try this out?:

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOneHundredRandomCodes` ()
BEGIN
    DECLARE ctr INT DEFAULT 0;
    hundred_loop:LOOP
        SET ctr = ctr + 1; -- increment
        -- insert command here
        INSERT INTO codes (codes)
        SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 5) AS codes;
        IF ctr = 100 THEN -- check if we should stop
            LEAVE hundred_loop;
        END IF;
    END LOOP hundred_loop;
END//

Create that procedure then run this command:

CALL InsertOneHundredRandomCodes();

It should insert into codes 100 random values for the codes value.

How to generate random chars and insert with MySQL?

4 Answers4

Linked