SELECT * FROM `tenant_user_info` WHERE id='1878788195659912196'
#查重
SELECT *,COUNT(0) AS id FROM `tenant_user_info` GROUP BY `phone`;
#新建函数#随机生成字符串
DELIMITER $$
CREATE FUNCTION rand_string3(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(52) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i<n DO
SET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i=i+1;
END WHILE;
RETURN return_str;
END $$
DELIMITER $$
CREATE FUNCTION rand_num2() RETURNS INT(6)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=FLOOR(1000+RAND()*1000000);
RETURN i;
END $$
#随机数字
DELIMITER $$
CREATE FUNCTION rand_num3() RETURNS INT(20)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=FLOOR(10000+RAND()*100000000);
RETURN i;
END $$
#随机生成手机号
#CREATE DEFINER=`root`@`%` FUNCTION `generatePhone`() RETURNS char(11) CHARSET utf8
DELIMITER $$
CREATE FUNCTION generatePhone1() RETURNS CHAR(11)
BEGIN
DECLARE head VARCHAR(100) DEFAULT '000,156,136,176';
DECLARE content CHAR(10) DEFAULT '0123456789';
DECLARE phone CHAR(11) DEFAULT SUBSTRING(head, 1+(FLOOR(1 + (RAND() * 3))*4), 3);
#SET phone = CONCAT(phone, substring('156,136,123,456,789', 1+(FLOOR(1 + (RAND() * 4))*4), 3));
DECLARE i INT DEFAULT 1;
DECLARE len INT DEFAULT LENGTH(content);
WHILE i<9 DO
SET i=i+1;
SET phone = CONCAT(phone, SUBSTRING(content, FLOOR(1 + RAND() * len), 1));
END WHILE;
RETURN phone;
END $$
#第一步：向表 tenant_user_info 中插入数据
DELIMITER $$
CREATE PROCEDURE iot013()
BEGIN
DECLARE num INT ;
SET num = 1 ;
WHILE num < 3 DO
INSERT INTO `tenant_user_info` (id, tenant_id, account,phone,PASSWORD) #表的字段
VALUES(UUID_SHORT(),getRand(),rand_string3(5),generatePhone1(), '0e88e91a455c7515655d787b86365bbf5d2be80c'); #插入的表数值
SET num = num + 1 ;
END
WHILE ;
END$$
CALL iot013()$$
新建函数#随机6位数字
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `central_develop`.`getRand`()
RETURNS int(11)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
return FLOOR(RAND()*500000 + 500000);
END$$
