Mysql – Obfuscate data during migration with MySQL Workbench

You need to take the data from UTF-8 and convert it into UCS-2LE using something like iconv. For example, using the character in your example:

echo "010000: dcb3" | xxd -r -s -0x10000 | iconv -f "UTF-8" -t "UCS-2LE" | xxd
0000000: 3307 

Now I'm not sure what character UTF-8 \xdcb3 is, but apparently it's correct translation to UCS-2LE is \U0733. If you have \0xDCB3 in the SQL Server it means it was not translated into UCS-2LE before import. You should not have surrogates in the NVARCHAR fields, UCS-2 is "surrogate agnostic". See UCS-2 vs. UTF-16 (not quite Kramer vs. Kramer).

I'm not an expert in the MySQL tool set so I can't say what step is missing that was supposed to do the iconv.

Update

to locate the records with surrogates you must turn to the binary representation, since any character function will threat the surrogates as 'special':. Luckily the string manipulation functions work on binary too with the expected semantics. Eg. CHARINDEX:

insert into test(a) values  (N'a');
insert into test(a) values  (NCHAR(0xdc83));
insert into test(a) values  (N'b');
go

select * from test where charindex(0x83dc, cast(a as varbinary(8000))) > 0;

Condition 1 : You can define a composite primary key such as :

CREATE customerItem (
  customer_id INT,
  item_id INT,
  view_date DATETIME,  
  PRIMARY KEY (customer_id, item_id)
) 

You can then use INSERT ... ON DUPLICATE KEY UPDATE such as :

INSERT INTO customerItem (customer_id,item_id,view_date) VALUES (123,999,"2014-12-22")
ON DUPLICATE KEY UPDATE view_date = VALUES(view_date);

Condition 2 : A trigger seems the way to go. Inspired by this answer on SO I wrote this UNTESTED code which might do the job (or, at least, can be a good start) :

CREATE TRIGGER handleMaxRows BEFORE INSERT ON customerItem 
FOR EACH ROW
BEGIN
  IF (SELECT COUNT(*) FROM customerItem WHERE customer_id = NEW.customer_id) = 25 THEN
    DELETE FROM customerItem 
    WHERE customer_id = NEW.customer_id
    ORDER BY view_date 
    LIMIT 1;
  END IF;    
END;

Edit : Forgot DELETE condition inside the trigger.