Create MySQL Test Data For Varbinary Column

  • Posted on: 16 August 2014
  • By: Zhijun Chen

I recently came across a scenario where I needed to create hundreds of test data for a MySQL table with varbinary column defined.

Here is the table schema:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(100) NOT NULL,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(20) NOT NULL,
  `password` varchar(100) NOT NULL,
  `company_id` int(11) NOT NULL,
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dtype` varchar(10) NOT NULL,
  `uuid` varbinary(16) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_name` (`user_name`),
  KEY `user_company_key` (`company_id`),
  CONSTRAINT `user_company_key` 
  FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) 
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I used uuid() method to generate column value, I got an error of "Data too long for column".  Finally I managed to get around the problem using the following procedure.

DELIMITER $$
USE test_db$$
DROP PROCEDURE IF EXISTS addUserTest$$
CREATE PROCEDURE addUserTest()
	BEGIN
		DECLARE x INT;
		SET x = 1;
		WHILE x <= 500 DO
			INSERT INTO user (`user_name`, `first_name`, `last_name`, 
				`password`, `company_id`, `dtype`, `uuid`) VALUES 
				(CONCAT('test_user', x, '@example.com'), 'Test', 'User', 
				'LKJMoSCO3eQSFoHlni9ludxTBkBHpPmk', 1, 'USER', 
				UNHEX(REPLACE(UUID(),'-','')));
			SET x = x + 1;
		END WHILE;
	END$$
DELIMITER ;
CALL addUserTest;

unhex(replace(uuid(), '-', '')) generates suitable values for varbinary(16) column.
The concat() method concatenates strings together in MySQL while replace() method replaces one string with another.

Tags: 

Add new comment

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.