CODEWITHSUNDEEP
mysql
santa
santa

Reputation: 12512

MySQl field type

I have a table in my db where I store records of user actions. Currently the column that contains user IDs is set to int(11), however i am making some changes to my code where I will be adding temporary user IDs.

To differentiate the temporary IDs from the regular ones, I prepend 0 to the id.

Example: 4 -- regular user; 023 -- temporary

However when I populate this ID into ym table the zero gets discarded. What field type do I need to change it to to keep all IDs in tact?

Upvotes: 1

Views: 105

Answers (4)

Michael
Michael

Reputation: 1231

You could change it to an varchar if you want to prefix the id's with a 0

But you might want to try this. Add a new column:

ALTER TABLE `your_table`  ADD COLUMN `temp_id` INT(11) NULL AFTER `original_id`;

Then migrate your id's

UPDATE `your_table` SET temp_id = `original_id`;

Upvotes: 2

John Green
John Green

Reputation: 13435

You can't add a 0 before an int ( (01 == 1) -- mostly but I'm not going to get into the vagaries of that).

Just add a type column. You can always drop the column later.

Upvotes: 0

Aaron Hathaway
Aaron Hathaway

Reputation: 4315

I think you'll have to go with a varchar field but note that this will eliminate your auto_increment if you have one.

Upvotes: 1

Richard Schneider
Richard Schneider

Reputation: 35477

The user ID is an int and ints are binary numbers. A leading zero is the SAME as the number without a leading zero.

I would suggest negating the number to indicate a temporary id.

Upvotes: 0

Related Questions