我有一个表,我添加了一个名为phone
- 该表还有一个 id 设置为自动增量的主键。如何将随机值插入到电话列中,该值不会重复。以下 UPDATE 语句确实插入了随机值,但并非所有值都是唯一的。另外,我没有被卖掉,我投了phone
字段也正确,但在尝试使用 ALTER TABLE 命令将其设置为 int(11) 时遇到问题(主要是它运行正确,但在添加包含新电话号码的行时,插入的值被转换为不同的数字)。
UPDATE Ballot SET phone = FLOOR(50000000 * RAND()) + 1;
表规格
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| phone | varchar(11) | NO | | NULL | |
| age | tinyint(3) | NO | | NULL | |
| test | tinyint(4) | NO | | 0 | |
| note | varchar(100) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
-- tbl_name: Table
-- column_name: Column
-- chars_str: String containing acceptable characters
-- n: Length of the random string
-- dummy_tbl: Not a parameter, leave as is!
UPDATE tbl_name SET column_name = (
SELECT GROUP_CONCAT(SUBSTRING(chars_str , 1+ FLOOR(RAND()*LENGTH(chars_str)) ,1) SEPARATOR '')
FROM (SELECT 1 /* UNION SELECT 2 ... UNION SELECT n */) AS dummy_tbl
);
-- Example
UPDATE tickets SET code = (
SELECT GROUP_CONCAT(SUBSTRING('123abcABC-_$@' , 1+ FLOOR(RAND()*LENGTH('123abcABC-_$@')) ,1) SEPARATOR '')
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS dummy_tbl
);
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)