UUDI/GUID primary key in MySQL

 

We have an app (IONIC 2 phone app front-end, ASP .Net Core 2.0 Web Api backend with MySql Server).

The phone app needs to be able to work in offline mode, and be able to sync (up and down) when it has internet connection. I immediately realized the auto-incrementing int primary keys in the MySql database would be an issue, and thought about using GUID/UUID primary keys instead. But I am worried about performance. I have two questions:

  • What is the best way to store a UUID field in MySql? CHAR(36)? As far as I can see, MySql doesn't have a dedicated UUID data type? (I am using MySql Server 5.6.30)

  • Would it be a good idea to NOT make it a Primary Key, so that it's not UNIQUE? Surely this would speed up performance? And a UUID is almost guaranteed to be unique anyway, so there's no need for MySql Server to waste time checking for this...

    • Thanks

    Without having the full details I would suggest the following general workflow:

  • a user adds a new product
  • this results in a record being written to the local SQLite database
  • maintain a column called sync which keeps track of whether a local record has been pushed to the server
  • note that the products which the app displays is the sum of cached server data and SQLite data which has not yet been synched
  • when the app does a sync, it pushes new products first, then marks those SQLite records as having been synched
  • when it pulls the latest catalog, it will return the new products with their actual unique IDs as they appear in the remote MySQL database
  • if the app goes offline again, then just go back to the first step and do the same thing


    • The basic idea here is to just focus on keeping one version of the truth as much as possible. Don't task your app with assigning an ID to a product which the server knows nothing about. For one thing, would it be meaningful to assign such an ID and then the user decides to delete the product without ever synching?

    链接地址: http://www.djcxy.com/p/91464.html

    上一篇: 什么是GUID的字符串长度？

    下一篇: MySQL中的UUDI / GUID主键