Re: How to generate unique primary key in MySQL?

[Jim Lyons]

If you just don't want a primary key in your major data tables, then create
a table for the express purpose of generating primary keys that uses
auto_increment.  Something like:

create table myseq (x   serial;)


Each time you need a new key, get the next value from that table.  It would
be more like an Oracle sequence that an auto_increment.

On Mon, Apr 27, 2009 at 11:59 PM, yuan edit edit.y...@gmail.com wrote:

 I have a shopping cart table like this:

 CREATE TABLE shopping_cart(
 id VARCHAR(20) NOT NULL,
 product_id INT NOT NULL,
 product_quantity INT NOT NULL,
 ...
 ...
 user_id INT NOT NULL,
 current_timestamp TIMESTAMP,
 primary key (id)
 );

 I will not use auto_increment

 Is there other way to  generate unique primary key in MySQL?

 Thank you




-- 
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

Re: How to generate unique primary key in MySQL?

[Michael Dykman]

  I wish my id has the same length,auto_increment can do this?


 I have a idear to generate unique primary key:

 select concat(cast(unix_timestamp() as char) , cast(substr(rand(),3,4) as
 char(4)));

 Is this ok? any good idear?

Your routine does not really guarantee uniqueness,  If you mean that
you want your keys to have the same display length, then the 36
character string produced by UUID() satisfies this.

Alternatively, and much simpler, is to use the auto_increment which
will give you the efficiency of an integer primary key,  When you want
to display it format it for display using LPAD.  ie.:

mysql SELECT lpad(5,8,'0');
+---+
| lpad(5,8,'0') |
+---+
| 0005  |
+---+


-- 
 - michael dykman
 - mdyk...@gmail.com

 - All models are wrong.  Some models are useful.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

How to generate unique primary key in MySQL?

[yuan edit]

I have a shopping cart table like this:

CREATE TABLE shopping_cart(
id VARCHAR(20) NOT NULL,
product_id INT NOT NULL,
product_quantity INT NOT NULL,
...
...
user_id INT NOT NULL,
current_timestamp TIMESTAMP,
primary key (id)
);

I will not use auto_increment

Is there other way to  generate unique primary key in MySQL?

Thank you

Re: How to generate unique primary key in MySQL?

[yuan edit]

BTW,i am using MySQL 5.0

Re: How to generate unique primary key in MySQL?

[Michael Dykman]

Ok, I will ask the obvious question: why do you refuse to use
auto_increment?  If this was Oracle or Postgresql, of course we would
use sequences, but that isn't available in MySQL.  Personally, I would
not go to Rome to order the sushi.

However, there is the function uuid() which can be used ie.

SELECT uuid();

and produces a guaranteed unique 36 character sitrng, but this might
not be very efficient in joins as your dataset grows.

 - michael dykman

On Tue, Apr 28, 2009 at 12:59 AM, yuan edit edit.y...@gmail.com wrote:
 I have a shopping cart table like this:

 CREATE TABLE shopping_cart(
 id VARCHAR(20) NOT NULL,
 product_id INT NOT NULL,
 product_quantity INT NOT NULL,
 ...
 ...
 user_id INT NOT NULL,
 current_timestamp TIMESTAMP,
 primary key (id)
 );

 I will not use auto_increment

 Is there other way to  generate unique primary key in MySQL?

 Thank you




-- 
 - michael dykman
 - mdyk...@gmail.com

 - All models are wrong.  Some models are useful.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org