database [QUESTION] How to handle MySQL database NULL cells?

[PsiWray]

I have a database table that contains certain columns with  NULL s or values, depending on if the value has already been set or not for the current record. When I query the columns of a specific record that contains those NULL cells, the value contained in the Lua table returned inside the result has a false value instead of (what I expected to be) a nil value. The type of the MySQL table column is TIMESTAMP.

So my question is, am I doing something wrong and is there instead a way to correctly handle NULL cells inside a MySQL database or is it just like described above and there's nothing I can do about it?

Thanks for support.

Edited March 20, 2019 by PsiWray

[IIYAMA]

Not sure,

 

Doesn't blob help you out?

 

But it shouldn't be an issue. At the end they both represents NO in most lua conditions.

 

This method is in most scenarios  just asking for trouble:

if a == nil then

 

This one works just 99% of the time:

if not a then

 

Edited March 20, 2019 by IIYAMA

[PsiWray]

3 hours ago, IIYAMA said:

Not sure,

 

Doesn't blob help you out?

 

But it shouldn't be an issue. At the end they both represents NO in most lua conditions.

 

This method is in most scenarios  just asking for trouble:

if a == nil then

 

This one works just 99% of the time:

if not a then

 

I was thinking about a situation where you have a stored BOOL inside the MySQL table and you either have its value (which can be true/false) or you don't, in which case you execute different code. I'm not in that situation yet but I was curious about this topic. I also understand that it's a very rare situation.

Thanks for the answer.

[PsiWray]

Actually I've found the solution. When working with booleans, the query result is of type "number". That allows you to discriminate between existing values and NULL values. For example, if you query for a boolean column and you get a result of type boolean and of value false, that means the column doesn't have a value. If on the other hand, you receive a result of type number and value 1/0 then you can cast to boolean and the thing is done.

[savour]

about the Boolean check, I think it would be good to store it as it is "true/false" but in a string value, then when you acquire it again transform in through this simple check

dbResult = dbResult == "true" and true or false

--that's a shortcut for
dbResult = someRetrievedValue
if dbResult == "true" then
  dbResult = true
else
  dbResult = false
end

 

Edited March 25, 2019 by savour