Primary Key Type: int vs long

问题:

I know some software shops have been burned by using the int type for the primary key of a persistent class. That being said, not all tables grow past 2 billions. As a matter of fact, most don't.

So, do you guys use the long type only for those classes that are mapped to potentially large tables OR for every persistent class just to be consistent? What's the industry concensus?

I'll leave this question open for a while so that you can share with us your success/horror stories.

回答1:

Long can be advantageous even if the table does not grow super large, yet has a high turnover ie if rows are deleted/inserted frequently. Your auto-generated/sequential unique identifier may increment to a high number while the table remains small.

I generally use Long because the performance benefits are not noticeable in most of my projects, however a bug due to overflow would be very noticeable!

That's not to say that Int is not a better option for other people's scenarios, for example for data crunching or complex query systems. Just be clear of the risks/benefits and how they impact your specific project.

回答2:

I don't know about "burned". It's not difficult to change from int to long when you need to. The conversion is straight forward in SQL, and then it's just a search and replace in your client code (or make the change in your persistence layer, and then compile and see what breaks.) You're moving from one integer type to another, so you don't have to worry about subtle conversion issues or truncation..

Going from float to double would be a lot harder.

回答3:

Because Int will always be faster for Select/Sorts.

回答4:

I use Integer for my surrogate keys unless I have a need for them to be something else. It is not necessary to always use a Long if you don't have a need for it.

(I typically use JPA/Hibernate in my projects running against either Oracle 10g or MySQL 5.x databases.)