Will all numbers be 100% unique in a table if to use this simple algorithm?

My goal is to insert a new and unique(unique is very important) number into a MySQL table on the server every time upon an event on a user's machine, using ajax.

So, server's part on user's event is doing this (using php):

  • Finds a maximum value from the column in the db,
  • Adds 10 to a maximum value,
  • this is a new and unique (bigger than a maximum) value, we insert insert into a table.

Will all numbers be unique and go like 1, 11, 21, 31, if it starts from 1? I'm curious if inserting into the Table finishes before it starts performing another queue and coule be like 1, 11, 21, 21, 31, 41?

If it theoretically works like this (ordered by time)

  1. find max value from the column for the first user
  2. find max a value from the column for the second user (it will be the same)
  3. insert a (max+10) for the first user into the same table
  4. insert a (max+10) for the second user into the same table (it will be the same), then the results will be the same, and 1 value could be repeated twice or even more...

So, the question is: will all numbers be 100% unique? Depending on this I have to choose which algorithm to use for creating unique numbers.

Added: Is it possible to be sure with this algorythm and without using autoincrements? Autoincrement is used for another column. Holes between numbers are OK. The only requirement is that numbers should be different, but with some "delta" that is more than one. Sorry I didn't notice about that in my question. Thank you.

--------------Solutions-------------

Unless you have a very specific reason against it, I recommend using AUTO_INCREMENT - it will scale much better and actually leave fewer "holes" in the sequence of numbers than your approach.

And you are correct - your approach will not actually guarantee uniqueness in concurrent environment. One way to make your algorithm work is to have a UNIQUE constraint on your field (if it is not already PRIMARY KEY) and then repeatedly attempt to insert a new value - if it fails just generate a new value and try again and it will eventually succeed.

Use an auto incrementing column in the db

It sounds like you want to use autoincrement.

If you're using auto-increment on a separate column, you can still emulate it with something like the following.

insert into mytable (
column1,
column2,
fake_auto_incr
) select
'value for column1',
'value for column2',
max (fake_auto_incr) + 1
from mytable

Because the insert is a transactional statement, ACID databases will ensure the max+1 trick is always one greater then the current top value.

Keep in mind you'll need a slight adjustment for that to work on an empty table, since the query will return NULL in that case. This should suffice:

insert into mytable (
column1,
column2,
fake_auto_incr
) select
'value for column1',
'value for column2',
coalesce (max (fake_auto_incr), 0) + 1
from mytable

This forces the initial value to 1 on an empty table, otherwise it uses the next available value.

Category:mysql Time:2011-11-11 Views:0

Related post

  • How can I generate 100 unique alphanumeric codes with 8 values? 2014-04-08

    I need to create 100 unique, alphanumeric codes consisting of 8 values....so just for example if I took the letters A, B, C, D and the numbers 1, 2, 3, 4, I would need to create 100 random, unique codes utilizing those values. Can this be done with e

  • How can I reference content in Alfresco and ensure that the reference is 100% unique 2011-07-06

    I am working with the Alfresco web service and looking at using it with another web application for content management. Our other web application will need a way to reference content that exist in Alfresco. Are the Reference uuid's that alfresco gene

  • Regex to allow only numbers between 100 and 999999 2011-08-24

    Can anyone help with C# code using regular expressions to validate a textbox which accepts only numbers between 100 and 999999 Thanks, Lui. --------------Solutions------------- You don't need a regex for this. int n; if (!int.TryParse(textBox.Text.Tr

  • Retrieve unique data from table column and sorting array 2012-03-05

    There is one table , and I want the unique values of table column data, like table row may have 4, 2 , 2, 4,5 , 4. So it should return array like this data = [ 2 , 4 , 5 ] I am able to achieve this using jquery as below link http://jsbin.com/ojeroc/5

  • Unique value in table using EF4 and locking 2010-06-09

    In this code, I have an Entity Framework 4 model with a single "Thing" entity that has an Id and a Name(string) column. I would like to ensure that when I call FindOrCreateThing(name) from multiple threads, only one row in the Things table will ever

  • getting schema + first 100 records from every table in a db 2009-11-12

    i have a large sql server db, and i want to get the schema (all tables/triggers/sprocs), i'm pretty sure that's easy. but the tough part is that i want to get 100 records from each table. it's a huge db on a remote server and i can't develop locally

  • SQL: select all unique values in table A which are not in table B 2010-11-15

    I have table A Id | Name | Department ----------------------------- 0 | Alice | 1 0 | Alice | 2 1 | Bob | 1 and table B Id | Name ------------- 0 | Alice I want to select all unique Ids in table A which do not exist in table B. how can I do this? ---

  • How do I guarantee uniqueness of a table value only within a subset of the table rows? 2011-10-01

    I have a table of projects belonging to various users: project_id, owner_user_id, project_name I do not need the project_names to be globally unique to the table, so making project_name UNIQUE does not help. I would just like to prevent the user from

  • MySQL dilemma: composite unique key across tables 2011-10-20

    I am attempting to implement an "extension" table structure for some stats that I am gathering from multiple sources. My "parent" table looks something like this: `test_parent` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `a

  • "A duplicate value cannot be inserted into a unique index. [ Table name = Order,Constraint name = PK_Order ]" 2011-12-04

    I am building a Windows Phone Application using Mango with SQLCE 4.0 (I think). I get this error when I am trying to submit a new OrderItem, with context.SubmitChanges() command. A duplicate value cannot be inserted into a unique index. [Table name =

  • Generating primary key that is unique across multiple tables 2011-12-24

    I am working on a restaurant app using Hibernate. I have several entities like Pizza, Beverage, Pasta etc. I want these to be persisted to different tables but have primary keys that are unique among all. i.e if I say itemId 4 it should be sufficient

  • In what situation, if we add sorted numbers as keys to a hash table, we can expect the hash to be ordered? 2012-05-02

    Is it true that in PHP, when we insert elements into a new hash table using sorted numbers as the keys, then the resulting hash will also be ordered? So when we get the keys, they will be ordered, and $a[0], $a[1], $a[2] will also follow that origina

  • Page numbers are wrong after I add table of contents 2012-12-03

    I wrote my book and then added a footer with page numbers. I then entered the table of contents. The first page of the book (I thought) would be 1 but the table of contents starts at 1, so that all pages after that are incorrect. The table of content

  • Word will not recognize any numbers past 2.0 for my table of contents. 2013-06-08

    MSW 2010 TOC Function Won't Work I am using MS Word 2010. My titles and paragraphs are numbered automatically. MS Word will not recognize any numbers past 2.0 for my table of contents. MSW will not input Sections 3.0 thru 9.0. I finally input the TOC

  • how to check whether the number ends with 9 or not in numbers 1to 100 2009-11-08

    How can I to print the numbers from 1 to 100 in the following format? 1-------------9 10------------19 20-------------29 30--------------39 40-----------49 50-----------59 60-----------------69 70---------------79 80---------------89 90--------------

  • New datatype which can have numbers upto 100 digits 2010-06-18

    Note: This was an interview question and may not have an actual use case currently The question was to design a class that can store numbers which are very very large say each number can have 100 digits. This new class is a datatype like int. What ar

  • Regex to validate numbers 0-100 with up to two decimal places 2011-10-18

    So I know it would be easier to just use the php is_numeric function, but what i'm trying to do is create a regular expression, mostly for learning regex, to test test scores. The range can be 0-100, but can have 2 decimal places. This is what I have

  • Numbering to 100+ in Word 2012-09-24

    Hello, I just registered and this is my first post. My operating system is Vista. This computer was obtained around 2007. I created a narrative list already and would ideally like to go back and number it to 100. This may be impossible, so at least I

  • Generating random numbers 1-100 2013-06-01

    I need to generate random numbers from 1 to 100 and I know how to do that part... I need to ask user how many numbers he wants to generate(if he says 5 the program needs to generate 5 numbers from 1 to 100). I only now how to make a fixable amount by

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.629 (s). 13 q(s)