PHP和MySQL，如何在“稀疏”表中选择字段，避免空值

Question

I have one table with hundreds of columns, but almost all are null.

Each 'select' in this table return only one line, with hundreds of fields.

Working with PHP, I am testing each field with:

if (!empty($dataset["field"])) { ... }

For optimization reasons, If I select only the valued fields I can use array functions to know the name of each field, and speed up my application.

But how can I select only the fields with values in this case, with MySQL 5.6 ?

something like:

SELECT * FROM mytable ONLY fields_not_null();

This table records the amount of events per minute during one day. The recordset is basically:

1. one field for date
2. 60x24 fields for minutes in the day (1440 fields)

Change it to 24 tables (for each hour) decrease the performance, and we still have dozens of null fields in each table.

Change it to one recordset with date_time and boolean is the worst scenario for performance.

Answer 1

You can try using col IS NOT NULL: SELECT * FROM mytable WHERE column IS NOT NULL