Jump to content

[QUESTION] How to handle MySQL database NULL cells?


PsiWray

Recommended Posts

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 by PsiWray
Link to comment
  • Moderators

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 by IIYAMA
Link to comment
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.

  • Like 1
Link to comment

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.

Link to comment

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 by savour
Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...