MySQL Alter表，添加具有唯一随机值的列 [英] MySQL Alter table, add column with unique random value

问题描述

我有一个表，其中添加了名为phone的列-该表还具有一个ID设置为auto_increments的主键.如何将随机值插入电话栏，该值将不会重复.以下UPDATE语句确实插入了随机值，但并非所有值都是唯一的.另外，我也没有卖出正确地转换phone字段的方法，但是在尝试将其设置为带有ALTER TABLE命令的int(11)时遇到了问题(主要是，它正确运行了，但是在添加一行时使用新的电话号码，则插入的值将转换为其他号码.

I have a table that I added a column called phone - the table also has an id set as a primary key that auto_increments. How can I insert a random value into the phone column, that won't be duplicated. The following UPDATE statement did insert random values, but not all of them unique. Also, I'm not sold I cast the phone field correctly either, but ran into issues when trying to set it as a int(11) w/ the ALTER TABLE command (mainly, it ran correctly, but when adding a row with a new phone number, the inserted value was translated into a different number).

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    |                |
+------------+--------------+------+-----+---------+----------------+

推荐答案

尝试一下

UPDATE Ballot SET phone = FLOOR(50000000 * RAND()) * id;

这篇关于MySQL Alter表，添加具有唯一随机值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！