Golang中的MySQL空值

Question

How have you solved the issue around LEFT JOIN's and null values with MySQL and Go?

For example I have two tables order and order_item. All columns have the not null attribute. An order can have 0 or more order_items. A simple select, left join query of an order without any order_items fails Row.Scan() because the order_item columns come back as null:

SELECT * FROM `order`
LEFT JOIN `order_item` USING (order_id)
WHERE `order`.order_id = '123'

I can see three solutions, none that I think are really good:

1. Use COALESCE/IFNULL => Annoying because my select part of the query is automatically generated.
2. Use the null values from mysql go lib => This will mean that every value in my order_item struct will be of these types.
3. Split the query into 2 => Problem if I want a list of 50 orders with order_items, that will be 51 queries.

Any other solutions you have?

Answer 1

You can use option 2 but instead of scanning into your struct and having to change the types of your struct, you can scan into temporary variables with nullable datatypes and based on the validity of those variables, copy their values into your struct with non-nullable types.