Generate unique 10-digit number

I want to generate customer ids for invoices and thus don't want to start counting from 1 for obvious reasons. In MySQL can you generate a random number that is unique?

I know about the RAND() function, but it does not guarantee uniqueness. What's the right approach for 开发者_如何转开发this?

Doesn't work:

INSERT INTO test (number) VALUES (FLOOR(0 + (RAND() * 9999999999)));

PS: Server side I'm using PHP and generate invoices using FPDF.

I suggest an AUTO_INCREMENT column and seed the value at 10 digits. You could have it be the only column in the table, like below, or more practically seed your invoice table id.

CREATE TABLE tablename (
  id bigint unsigned not null auto_increment,
  primary key(id),
  auto_increment=1000000000
);

Actually, the reasons aren't that obvious unless it's simply that you don't want your customers to know there are so few that they all have one-digit IDs :-)

As an aside, the customer ID is usually generated when adding a customer rather than doing an invoice. If you meant an invoice number, that's different, but the reasoning will be the same.

Either populate the table with a dummy entry with a suitable lower ID (314159 for example) then use something like:

insert into test (number) select max(number)+1 from test

or use an auto incrementing key with a suitable starting value (again, this value is up to you but you should choose something relatively "non-round").

I've sometimes used the former for situations where I want total control over what values are assigned (including the ability to easily change them) but a more robust solution would probably be the auto-increment one.

ALTER TABLE my_invoice_table auto_increment=1000000001

Incrementing invoice numbers are fine - they only have to be unique, not random.