We created a simple SQL to update all urls replacing common characters and avoid duplicated (max 3) urls.
Code: Select all
DELETE FROM url_alias WHERE 1;
INSERT INTO `url_alias`
SELECT null as `url_alias_id`,
concat('product_id=',`product_id`) as query,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(lower(`name`),':',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'*',''),'(',''),')',''),'+',''),'{',''),'}',''),'[',''),']',''),';',''),'''',''),':',''),'"',''),'<',''),'>',''),'?',''),' ','-'),'><',''),'<>','') as keyword FROM `product_description` WHERE language_id=1;
INSERT INTO `url_alias`
SELECT null as `url_alias_id`,
concat('category_id=',`category_id`) as query,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(lower(`name`),':',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'*',''),'(',''),')',''),'+',''),'{',''),'}',''),'[',''),']',''),';',''),'''',''),':',''),'"',''),'<',''),'>',''),'?',''),' ','-'),'><',''),'<>','') as keyword FROM `category_description` WHERE language_id=1;
UPDATE url_alias SET keyword=concat(keyword,'2')
WHERE url_alias_id NOT IN (SELECT *
FROM (SELECT MIN(n.url_alias_id)
FROM url_alias n
GROUP BY n.keyword) x);
UPDATE url_alias SET keyword=concat(keyword,'3')
WHERE url_alias_id NOT IN (SELECT *
FROM (SELECT MIN(n.url_alias_id)
FROM url_alias n
GROUP BY n.keyword) x);