mysql Insert a random number into a certain field (insert a random number into the MySQL database)_MySQL

Commonly used codes

UPDATE `表名` SET `字段名`=ceiling(rand()*500000+500000) WHERE (条件); 

update `表名` set click=click*0.01 where classid='2' and click>2000

We often encounter problems using random numbers. Here is a way to solve random numbers.

　When constructing test data, we need to insert random data into the test table. The method of constructing test data is as follows, taking update as an example

Step 1: The SQL function of random numbers is rand(), and rand() generates decimals between 0-1.

Step 2: Set rand()*10

Will produce numbers with decimals between 1-10, which can be converted using ceil.

Step 3: Use cast for type conversion

The usage of Cast is as follows:

　Cast（value as type）

　Binary, with the effect of binary prefix: BINARY

　Character type, can take parameters: CHAR()

　Date: DATE

　Time: TIME

　Date time type: DATETIME

　Floating point number: DECIMAL

　Integer: SIGNED

　Unsigned integer: UNSIGNED

SELECT CAST(RAND()*10 AS signed) AS rand

Step 4: Run sql statement

UPDATE storm_effect_info s SET

　s.fhtp = (SELECT CAST(RAND()*10 AS signed) AS rand)WHERE s.id = 13