Блог

У меня есть хранимая процедура, которую я использовал для «деидентификации» информации о клиенте, когда я хочу использовать ее в тестовой среде. Я заменяю фактические имена и адреса случайными значениями. У меня есть таблицы базы данных в базе данных с именем dict (для словаря) для женских имен, мужских имен, фамилий и адресов.

У каждого из них есть поле с именем f_row_id, которое представляет собой последовательный номер от 1 до x, по одному для каждой записи в таблице.

Недавно мы обновились до MySQL 8, и хранимая процедура перестала работать. В итоге я получил значение NULL для каждого поля, в котором я пытался заполнить случайное значение из другой таблицы. Пытаясь найти то, что теперь будет работать, я не могу заставить следующий запрос работать так, как я ожидаю:

 SELECT
    f_enroll_id,
    (SELECT f_name FROM dict.dummy_female_first_name fn WHERE fn.f_row_id = (FLOOR(RAND() * 850)    1) LIMIT 1)
FROM
    t_enroll
  

Моя таблица данных (которую я в конечном итоге хочу содержать со случайными именами) называется t_enroll . В этом есть поле ID (f_enroll_id) Я хочу получить список каждого идентификатора и случайное имя для каждой записи в этой таблице.

В таблице случайных имен (dummy_female_first_name) 850 записей (в моей хранимой процедуре это переменная сеанса, которую я вычисляю в начале процедуры).

Когда я впервые попытался запустить это, я получил сообщение об ошибке, что мой подзапрос вернул более одного значения. Я не понимаю, зачем это делать, поскольку (FLOOR(RAND() * 850) 1) должен возвращать одно целое число. Поэтому я добавил ОГРАНИЧЕНИЕ 1. Но когда я запускаю это, около половины возвращаемых строк имеют значение NULL для первого имени.

Я проверил, что все строки в моей таблице имен имеют идентификатор строки, что идентификатор строки уникален, и в числах нет пробелов.

Как вы думаете, что является причиной этого?

Заранее спасибо!

Вот схема для таблицы, которую я обновляю:

 CREATE TABLE `t_enroll` (
  `f_enroll_id` int(15) NOT NULL AUTO_INCREMENT,
  `f_status` int(2) DEFAULT NULL,
  `f_date_enrolled` date NOT NULL DEFAULT '0000-00-00',
  `f_first_name` varchar(20) DEFAULT NULL,
  `f_mi` char(1) DEFAULT NULL,
  `f_last_name` varchar(20) NOT NULL DEFAULT '',
  `f_maiden_name` varchar(20) DEFAULT NULL,
  `f_dob` date NOT NULL DEFAULT '0000-00-00',
  `f_date_fee_received` date NOT NULL DEFAULT '0000-00-00',
  `f_gender` int(11) NOT NULL DEFAULT '2',
  `f_address_1` varchar(40) DEFAULT NULL,
  `f_address_2` varchar(20) DEFAULT NULL,
  `f_quadrant` char(2) DEFAULT NULL,
  `f_city` varchar(25) DEFAULT NULL,
  `f_state` char(2) NOT NULL DEFAULT '',
  `f_county` varchar(3) NOT NULL,
  `f_zip_code` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`f_enroll_id`),
  KEY `f_date_enrolled` (`f_date_enrolled`),
  KEY `f_last_name` (`f_last_name`),
  KEY `f_first_name` (`f_first_name`),
  KEY `f_dob` (`f_dob`),
  KEY `f_gender` (`f_gender`)
ENGINE=InnoDB AUTO_INCREMENT=532 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 15360 kB';
  

Вот схема для таблицы словаря, из которой я извлекаю имена:

 CREATE TABLE `dummy_female_first_name` (
  `f_row_id` int(11) NOT NULL,
  `f_name` varchar(25) NOT NULL,
  PRIMARY KEY (`f_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  

Как я упоминал в своем комментарии, я нашел альтернативный подход, используя вариант ORDER BY RAND() LIMIT 1 . Но мне все еще любопытно, что происходит, из-за чего мой оригинальный метод не сработал. Это то, что изменилось в более поздней версии MySQL, потому что раньше это работало.

Еще раз спасибо.

Это гораздо более дорогостоящий подход, но вы можете использовать:

 SELECT f_enroll_id,
        (SELECT f_name FROM dict.dummy_female_first_name fn ORDER BY rand() LIMIT 1)
FROM t_enroll;
  

Вы можете сделать это более эффективным, используя:

 SELECT f_enroll_id,
        (SELECT f_name
         FROM dict.dummy_female_first_name fn 
         WHERE rand() < 0.01
         ORDER BY rand() LIMIT 1
        )
FROM t_enroll;
  

where Предложение означает, что будет отфильтровано около 8 строк, поэтому сортировка будет намного быстрее.