2

I am trying to create MySql column that I want to be a five digit integer. The first two digits I want to use from my software and the last three to generate from dabatabase.

Example: Store number 10 will be 10000 than 10001, 10002 for the other store ex: Store number 20 will be 20000, 20001, 20002 ...

Pedro Romano
  • 10,973
  • 4
  • 46
  • 50
Fi3n1k
  • 863
  • 3
  • 12
  • 20
  • why you just do not want to create two columns? This is a more logical solution. – defuz Oct 10 '12 at 07:28
  • I cant because there might be two orders with 001 from two different stores as ex: 10-001 and 20-001 – Fi3n1k Oct 10 '12 at 07:35
  • 2
    make unique index with two columns: storage_id and order_id – defuz Oct 10 '12 at 07:53
  • I dont think this will solve my problem but anyway thanks – Fi3n1k Oct 10 '12 at 07:59
  • I think you are asking how to create multiple sequences/autoincrements in MySql ? If that is right then I think this is wha you need to think about - http://stackoverflow.com/questions/9046971/mysql-equivalent-of-oracles-sequence-nexval – Hugh Jones Oct 10 '12 at 08:13

2 Answers2

1

Make the order_id an autoincrement field and then make a primary key on store_id and order_id (in that order).

This way the order_id will count separately for each store_id.

See this example: http://sqlfiddle.com/#!2/33b3e/1

full code:

CREATE TABLE order_ticket_number ( id_store_ticket int(10) NOT NULL,
                                  id_order_ticket int(10) AUTO_INCREMENT NOT NULL,
                                  id_order int(10) unsigned NOT NULL default 0, 
                                  PRIMARY KEY (id_store_ticket,id_order_ticket)

                                   )
ENGINE=myisam DEFAULT CHARSET=utf8;


INSERT INTO order_ticket_number (id_store_ticket) VALUES (10),(10),(20),(20);

Edit: This can only be done with MyIsam and (apparently) not with InnoDB.

So I think there are two options. Either handle this in your application logic or create a MyIsam table just to handle the numbering. Once you have inserted in there, you'll know the order_id and you can insert it into the InnoDB table. Although this does not seem like the most elegant solution. I think it's far more error proof than trying to generate it yourself (racing conditions).

Last thing you should be asking yourself is why you would want to have these numbers. Why not use a simple autoincrement for each order regardless of the store_id....

Nin
  • 2,960
  • 21
  • 30
  • I cannot disagree with this approach but there is a real scalability problem looming in the OPs question - the system can accept a maximum of 999 orders either per-store(if you do it the way it is originally described) or in total if you have just the one autoincrement for all stores. – Hugh Jones Oct 10 '12 at 08:34
  • Sure you can, it will just go from 10-999 to 10-1000. But if he limits it to three digits, then yes he can only have 999 order per store. But that will be true for every solution posted here if that is the restriction.... – Nin Oct 10 '12 at 08:43
  • I created table with two PK (store_id and order_id). order_id is ok ex: starts with 001 for both stores as 10-001 and 20-001 but when I try to insert another row with same store ex. 10 - 002 (since it is autoincrement) then it doesnt allow because of duplication for store_id (ex.10 ) – Fi3n1k Oct 10 '12 at 10:26
  • Show the structure of the table (or compare it with the sqlfiddle example I gave). – Nin Oct 10 '12 at 10:28
  • CREATE TABLE `order_ticket_number` ( `id_store_ticket` int(10) NOT NULL, `id_order_ticket` int(10) NOT NULL, `id_order` int(10) unsigned NOT NULL, PRIMARY KEY (`id_order_ticket`,`id_store_ticket`), KEY `fk_id_order_1` (`id_order`), CONSTRAINT `fk_id_order_1` FOREIGN KEY (`id_order`) REFERENCES `order` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8$$ – Fi3n1k Oct 10 '12 at 10:34
  • But the id_order_ticket is not auto_increment? And the PK should be store_id, id_order_ticket) in that order! – Nin Oct 10 '12 at 10:35
  • I cannot set PK in this order(id_store_ticket,id_order_ticket) is sets automatically as oposite – Fi3n1k Oct 10 '12 at 11:44
  • by the way the code : id_order_ticket int(10) AUTO_INCREMENT NOT NULL, I want to start at 000 (3digit number and not start at 1 ). I set AUTO_INCREMENT = 000 NOT NULL but it can not execute query – Fi3n1k Oct 10 '12 at 12:56
  • and if i do AUTO_INCREMENT = 000 will it be an error at 999 or it will continue to 0000 (4 digit). Thanks – Fi3n1k Oct 10 '12 at 12:57
  • @nin - I like the look of the SqlFiddle site but cannot run your example - it sticks on a 'Loading' popup with the example greyed out in the background - any thoughts ? – Hugh Jones Oct 10 '12 at 13:19
  • @Fi3n1k: Use id_order_ticket int(3) ZEROFILL AUTO_INCREMENT NOT NULL HughJones: you have js enabled? Otherwise I have no clue. – Nin Oct 10 '12 at 13:25
  • @Nin: It is ok autoincrement but starting at 1. I want order_id to start with 001 ... – Fi3n1k Oct 11 '12 at 09:41
  • @Nin thank you works perfect. I am programming in Python and can I restart this number 001 ex.every day when I open store. Example today I arrive till order.556 and I close store. Tomorrow if I open again I want to start from 001 again. Meaning this number to be restarted when I make SOD(start of Day from programm). It is not problem in Python but I need help for MySQL – Fi3n1k Oct 11 '12 at 10:29
  • Please accept this answer if it works. If you have additional questions, post a new question. You can not have two records with shop 10 and order 001 so you cannot restart each day. Read the last remark in my answer – Nin Oct 11 '12 at 10:38
  • Logically yes. But can I also put date and make PK with 3 field and than to have two rows with ex. 10-001, or do you have any suggestion. Thanks a lot. – Fi3n1k Oct 11 '12 at 10:46
  • Yes. Why don't you try it? But really think about your structure. But there are too many comments already. Please post new questions as new question. – Nin Oct 11 '12 at 10:50
0

As suggested in the comments, do consider that approach. Simply have 2 columns, and bind them through UNIQUE so there's no conflicts. If you look for the 1st id in Store ID 10, simply WHERE store_id = 10 AND other_id = 1. It's more logical, and you can make a simple function to output this as 100001:

function store_string($int_store_id, $int_other_id) {
    $str = str_repeat('0', (2 - strlen($int_store_id))).$int_store_id;
    $str .= str_repeat('0', (3 - strlen($int_other_id))).$int_other_id;
    return $str;
}

(PHP example, but simply look up strlen and str_repeat to get the idea.

This gives you a lot of advantages such as easier searching for either value, and the possibility to go beyond store_id 99 without having to alter all existing rows and just the output function.

Regarding the actual INSERT, you can run your inserts like this:

INSERT INTO table_name (store_id, other_id, third_value)
SELECT {$store_id}, (other_id + 1), {$third_value}
FROM (( SELECT other_id
        FROM table_name
        WHERE store_id = {$store_id}
        ORDER BY other_id DESC)
        UNION ALL
    (   SELECT '0')
        LIMIT 1) AS h

And simply extend with more values the same way $third_value is used.

Robin Castlin
  • 10,956
  • 1
  • 28
  • 44