Mysql inserts a random number into a field (inserts a random number to the MySQL database) and a random number to mysql

Mysql inserts a random number into a field (inserts a random number to the MySQL database) and a random number to mysql

Common Code

UPDATE 'table name 'set' field name' = ceiling (rand () * 500000 + 500000) WHERE (condition );

Update 'table name' set click = click * 0.01 where classid = '2' and click> 2000

We often encounter the use of random, the following is a method to solve the random number.

When constructing test data, we need to insert random data into the test table. The following describes how to construct test data: update

Step 1: The SQL function of the random number is rand (), while rand () generates decimal places between 0 and 1.

Step 2: Set rand () * 10

A decimal number between 1 and 10 can be converted using ceil.

Step 3: Use cast for type conversion

Cast is used as follows:

Cast (value as type)

Binary, with a BINARY Prefix: binary

Character type, with parameters: CHAR ()

DATE: DATE

TIME: TIME

DATETIME type

Floating Point: DECIMAL

Integer: SIGNED

UNSIGNED integer

Select cast (RAND () * 10 AS signed) AS rand

Step 4: run the SQL statement

UPDATE storm_effect_info s SET

S. fhtp = (select cast (RAND () * 10 AS signed) AS rand) WHERE s. id = 13