Reputation: 321
Modify MySQL database id numbers to be all odd or all even
I currently have two separate MySQL databases, one for a UK based website, and the other for an IE based website. I have set up a multi-store system on the actual site so that everything can be managed from one admin interface, but now I need to merge the data from the two original databases. I need to modify things like customer id, address book id, order id, customer id so that they are not duplicated.
One option would be to modify, for instance, the customer id for database two so that they follow on from the last id of database one, but this isn't favourable as old customers from db2 are going to be numbered higher than new customers from db1.
My preferred option would be to run some commands on each of the original databases so that in db 1, all id numbers become odd, and on db2, all id numbers become even.
As an example, this is the structure of one table
CREATE TABLE `address_book` (
`address_book_id` int(11) NOT NULL,
`customers_id` int(11) NOT NULL DEFAULT '0',
`entry_gender` char(1) NOT NULL DEFAULT '',
`entry_company` varchar(64) DEFAULT NULL,
`entry_firstname` varchar(32) NOT NULL DEFAULT '',
`entry_lastname` varchar(32) NOT NULL DEFAULT '',
`entry_street_address` varchar(64) NOT NULL DEFAULT '',
`entry_suburb` varchar(32) DEFAULT NULL,
`entry_postcode` varchar(10) NOT NULL DEFAULT '',
`entry_city` varchar(32) NOT NULL DEFAULT '',
`entry_state` varchar(32) DEFAULT NULL,
`entry_country_id` int(11) NOT NULL DEFAULT '0',
`entry_zone_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
And example content in db1 is
INSERT INTO `address_book` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) VALUES
(1, 1, 'm', '', 'Site1', 'User1', '1 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 369),
(2, 2, 'm', '', 'Site1', 'User2', '2 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 197),
(3, 3, 'm', '', 'Site1', 'User3', '3 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 235);
example content in db2 is
INSERT INTO `address_book` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) VALUES
(1, 1, 'm', '', 'Site2', 'User1', '10 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 369),
(2, 2, 'm', '', 'Site2', 'User2', '20 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 197),
(3, 3, 'm', '', 'Site2', 'User3', '30 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 235);
Desired result from modification query would be to end up with content like this
INSERT INTO `address_book` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) VALUES
(1, 1, 'm', '', 'Site1', 'User1', '1 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 369),
(3, 3, 'm', '', 'Site1', 'User2', '2 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 197),
(5, 5, 'm', '', 'Site1', 'User3', '3 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 235);
INSERT INTO `address_book` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) VALUES
(2, 2, 'm', '', 'Site2', 'User1', '10 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 369),
(4, 4, 'm', '', 'Site2', 'User2', '20 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 197),
(6, 6, 'm', '', 'Site2', 'User3', '30 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 235);
which would then allow me to import both sets of data without any duplicated id numbers, and it would allow the latest entries from both original databases to have newer id numbers.
One thing to note is that these id numbers are not necessarily sequential. There can be gaps in the order due to old/spam accounts being deleted. I don't mind if the end result doesn't retain these gaps in id numbers, in fact it would be preferable if it didn't, but not a deal breaker if that can't be done.
Is this possible without totally messing up the data?
Upvotes: 1
Views: 86
Answers (1)
Reputation: 49385
It simple Math, but it could take some time to process, depending on the number
Also you didn't mention any related table, where you also have to apply the algorithm
If you have for all linked tables a foreign Key with ON UPDATE CASCADE it will automatically update all ids, but that sadly will not work with MyIsam
CREATE TABLE `address_book` ( `address_book_id` int(11) NOT NULL, `customers_id` int(11) NOT NULL DEFAULT '0', `entry_gender` char(1) NOT NULL DEFAULT '', `entry_company` varchar(64) DEFAULT NULL, `entry_firstname` varchar(32) NOT NULL DEFAULT '', `entry_lastname` varchar(32) NOT NULL DEFAULT '', `entry_street_address` varchar(64) NOT NULL DEFAULT '', `entry_suburb` varchar(32) DEFAULT NULL, `entry_postcode` varchar(10) NOT NULL DEFAULT '', `entry_city` varchar(32) NOT NULL DEFAULT '', `entry_state` varchar(32) DEFAULT NULL, `entry_country_id` int(11) NOT NULL DEFAULT '0', `entry_zone_id` int(11) NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `address_book` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) VALUES (1, 1, 'm', '', 'Site1', 'User1', '1 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 369), (2, 2, 'm', '', 'Site1', 'User2', '2 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 197), (3, 3, 'm', '', 'Site1', 'User3', '3 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 235);
UPDATE address_book SET address_book_id = (address_book_id - 1) * 2 + 1
SELECT * FROM address_bookaddress_book_id | customers_id | entry_gender | entry_company | entry_firstname | entry_lastname | entry_street_address | entry_suburb | entry_postcode | entry_city | entry_state | entry_country_id | entry_zone_id --------------: | -----------: | :----------- | :------------ | :-------------- | :------------- | :------------------- | :----------- | :------------- | :--------- | :---------- | ---------------: | ------------: 1 | 1 | m | | Site1 | User1 | 1 Any Street | Any Town | ANO1TH | Any County | | 222 | 369 3 | 2 | m | | Site1 | User2 | 2 Any Street | Any Town | ANO1TH | Any County | | 222 | 197 5 | 3 | m | | Site1 | User3 | 3 Any Street | Any Town | ANO1TH | Any County | | 222 | 235
CREATE TABLE `address_book2` ( `address_book_id` int(11) NOT NULL, `customers_id` int(11) NOT NULL DEFAULT '0', `entry_gender` char(1) NOT NULL DEFAULT '', `entry_company` varchar(64) DEFAULT NULL, `entry_firstname` varchar(32) NOT NULL DEFAULT '', `entry_lastname` varchar(32) NOT NULL DEFAULT '', `entry_street_address` varchar(64) NOT NULL DEFAULT '', `entry_suburb` varchar(32) DEFAULT NULL, `entry_postcode` varchar(10) NOT NULL DEFAULT '', `entry_city` varchar(32) NOT NULL DEFAULT '', `entry_state` varchar(32) DEFAULT NULL, `entry_country_id` int(11) NOT NULL DEFAULT '0', `entry_zone_id` int(11) NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `address_book2` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) VALUES (1, 1, 'm', '', 'Site1', 'User1', '1 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 369), (2, 2, 'm', '', 'Site1', 'User2', '2 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 197), (3, 3, 'm', '', 'Site1', 'User3', '3 Any Street', 'Any Town', 'ANO1TH', 'Any County', '', 222, 235);
UPDATE address_book2 SET address_book_id = address_book_id * 2
SELECT * FROM address_book2address_book_id | customers_id | entry_gender | entry_company | entry_firstname | entry_lastname | entry_street_address | entry_suburb | entry_postcode | entry_city | entry_state | entry_country_id | entry_zone_id --------------: | -----------: | :----------- | :------------ | :-------------- | :------------- | :------------------- | :----------- | :------------- | :--------- | :---------- | ---------------: | ------------: 2 | 1 | m | | Site1 | User1 | 1 Any Street | Any Town | ANO1TH | Any County | | 222 | 369 4 | 2 | m | | Site1 | User2 | 2 Any Street | Any Town | ANO1TH | Any County | | 222 | 197 6 | 3 | m | | Site1 | User3 | 3 Any Street | Any Town | ANO1TH | Any County | | 222 | 235
db<>fiddle here
Upvotes: 1
Related Questions
- Replacing negative id's with correct positive Ids?
- How can solve this mysql unique id providing odd numbers?
- how can i change the start id number in mysql
- MySQL Auto increment: Is there any way to reassign IDs starting from 1?
- MySQL odd and even auto-incremented IDs
- how to get even odd ids in different columns on single query?
- SQL WHERE id is odd?
- MySQL: Auto increment with even or odd numbers only?
- Adjust MySQL ID column +1
- MYSQL enumeration: @rownum, odd and even records