0

I have a table with the following column names:

id (Primary Key, AI) ccode(char15, Unique Constraint), version(tinyint 3)

I need to generate these unique codes and insert X codes at a time in the table, where X can vary from 1000 - 1 Million and this activity will be repeated over multiple times whenever the system admin wishes to generate more codes. The codes inserted need to be unique across lifetime of the app. I am using LAMP stack for this.

I first tried inserting one row at a time and the performance was bad. I have now moved to using bulk insert to speed up the process. I am contemplating on getting the count before doing the bulk insert, Using INSERT IGNORE to ignore the duplicate values , getting count after Bulk Insert and going through another round till I achieve the X codes required. Questions:

  1. Is there any other way to do this specially without using IGNORE
  2. How do I get the value of the codes which caused duplicate constraint warning
  3. Any other way to get the count of non duplicate inserts besides what I am doing?

Update: I am using the below logic to generate the codes: https://stackoverflow.com/a/13733588

Community
  • 1
  • 1
FBP
  • 345
  • 3
  • 15
  • Wouldn't it be easier just to ensure you generate unique codes? You don't say what programming/scripting language you are using, which would help suggest a mechanism for doing so. – Nick Apr 06 '17 at 12:50
  • @Nick Please see my update. I am using this http://stackoverflow.com/a/13733588 – FBP Apr 06 '17 at 12:55
  • A method I've used in the past, where security has not been paramount, is simply to prefix a string to the string version of the id field and then use the MD5 value of this as the 'unique' code. I've never had problem with duplicates. Something like SELECT MD5(CONCAT('confusion',id)). – Nick Apr 06 '17 at 13:02
  • Read this: [Count How Many Rows Inserted From Last SQL Query](http://stackoverflow.com/questions/12623778/count-how-many-rows-inserted-from-last-sql-query) – roetnig Apr 06 '17 at 13:27

0 Answers0