REST 设计困惑？Mysql 是直接存字符串好还是数字好？

Question

现在正在做 REST API 的设计，在设计过程中遇到了一些困惑，问题是这样的：

比如我有一个订单表

order_id	products	status
1	笔记本电脑	canceled
2	华为手机	finished
3	小米手环	delivering

获取所有订单的接口设计如下，其中有一个可选参数是 status 可选值为 canceled、finished、delivering 。

GET /order?status=canceled //已取消的订单

GET /order?status=finished//已结束的订单

GET /order?status=delivering //配送中的订单

这样设计API，可读性还是很好的。但对这里的 status 字段有一些疑问，该字段在数据库中，是直接存储为字符串？还是存储为数字？

由于需要保证API可读性好，所以如果用 数字来存储 status ，那么就需要管理字符串与数字之间的对应关系。

比如说：canceled => 0、finished => 1, delivering => 2

但是这样子，就需要在程序上，对status做一些转换，会无形增加一些程序的复杂性。

我的问题是：能不能直接把 status 字段直接存储为字符型？，就像上面的设计一样。如果数据量大会不会造成性能问题？或者其他一些问题？ 谢谢！

Answer 1

I now think it is better to store strings.
If you store numbers, according to your business process, store 0, 1, 2, 3, 4, 5, etc. to represent different states. It is no problem to define it this way in the early stage.
But if the business process changes and two new states need to be added between states 4 and 5, then a new state 6 and 7 will be added.
Actually, your process status is 0, 1, 2, 3, 4, 6, 7, 5, but it looks weird to me.

Using strings here eliminates this problem.

Answer 2

I save tinyint and then use a public method to map it in the program

Answer 3

I suggest that storing int instead of string will be much better in all aspects (except for the conversion you mentioned).

Then there is the issue of conversion. In fact, this is not a problem. All operations before contacting the db can use the nouns of your business (canceled, finished, delivering). It will only be converted when contacting the db. For the corresponding numbers, in fact, it is better to only write the conversion part in one place (for example, this part is the dao layer in the java design pattern). Of course, in order to facilitate the subsequent modification of nouns or to unify everyone’s spelling (uppercase and lowercase?) Nouns must also be defined as constants.

Answer 4

I won’t tell you that Taobao stores strings

Answer 5

If you store a string, if someone accidentally writes DELlVERING (l is lowercase, did you see it?) when writing code one day, you will be in trouble.

Generally my coding habit is, "Don't believe that others won't make mistakes, don't believe that you won't make mistakes, give the smallest choice."

Answer 6

Saving strings is a way to die.

Readability can be achieved by using constants or enumerations instead of hard coding in the code.