3 ms·
The result format of select * is unpredictable unless you are guaranteed to know your current schema. And if you know your current schema and want all columns,
by scrollaway 8y ago
The result format of select * is unpredictable unless you are guaranteed to know your current schema. And if you know your current schema and want all columns, for an ORM it's trivial to generate a list of all the columns you want, so there's no need to use star.
For example: Let's say you have a piece of code that does: id, value = query("select * from tbl").fetch_one(). Now that works great if you know you have exactly the columns id and value in there. But let's say they swap places (possible in mysql), or a third column is added, and you don't update your code alongside it. Now that code breaks. Had you done "select id, value from tbl", it wouldn't break.
PS: The sibling answers are also all correct; there's more reasons not to use select * outside of the shell.
- anderse 8y agoDo you mean your database driver doesn’t supply you with field names, that you rely on order to map up data?
- scrollaway 8y agoYour driver can probably introspect, yes. You can also probably introspect yourself if you want to. But all this to be less explicit about what you're querying seems like a waste, doesn't it?
- tejasmanohar 8y agoI guess I often rely on column order in Go, given Rows.Scan() [0]... so `select ` would be scary since someone may ALTER the table. Maybe that's the antipattern! But it's also a feature so I feel like you're better off avoiding the . Also, if someone adds additional big columns, you may incur additional, unnecessary cost [0]: https://golang.org/pkg/database/sql/#Rows.Scan https://golang.org/pkg/database/sql/#Rows.Scan
- paulmd 8y ago> for an ORM it's trivial to generate a list of all the columns you want, so there's no need to use star. But doctor, it hurts when I [don't use an ORM/ORM-generated queries]
- scrollaway 8y agoIf you're not using an ORM you especially shouldn't be using the star-op in code. Don't litter in the streets, don't litter in your code. If you make an unmaintainable mess and it hurts, you are entirely responsible for owning it.
- terandle 8y agoGotcha yes for sure in this example you would want to use the column names. In my database code I extract values by referencing the column names - not by their position. So neither swapping or adding a new column breaks the code. Well thank you for explaining your reasons.
- grogers 8y agoSelect * is useful if you are adding columns. You can either read the column or supply a default if the column doesn't exist. Doing this by having two queries naming different lists of columns is much worse, although it's unavoidable for mutations AFAIK.