How To Select Default Value Of A Field

I can't seem to find or write a sqlquery that SELECTS the DEFAULT VALUE (and I don't think I can generate it in phpmyadmin for me to copy) I tried to SELECT it as if it was a reco

Solution 1:

"SELECT $group FROM grouptable WHERE $group=DEFAULT( $group ) "

Or I think better:

"SELECT DEFAULT( $group ) FROM grouptable LIMIT 1 "

---

Update - correction

As @Jeff Caron pointed, the above will only work if there is at least 1 row in grouptable. If you want the result even if the grouptable has no rows, you can use this:

"SELECT DEFAULT( $group ) 
 FROM (SELECT 1) AS dummy
   LEFT JOIN grouptable 
     ON True
 LIMIT 1 ;"

Solution 2:

Get the default values of all fields in mytable in the associative array $res:

// MySQL v.5.7+$res = [];
$sql = "SHOW FULL COLUMNS FROM `mytable`";
foreach ($PDO->query( $sql, PDO::FETCH_ASSOC ) as$row) {
    $res[$row['Field']] = $row['Default'] ;
}
print_r($res);

Solution 3:

You can get the default column of any table, and in fact lots of interesting information about it, by looking at the INFORMATION_SCHEMA.COLUMNS tables. As the documentation states...

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. (Source: MySQL 8.0 Reference Manual / INFORMATION_SCHEMA Tables / Introduction.)

So, to get the column default, just SELECT COLUMN_DEFAULT, like...

SELECT COLUMN_DEFAULT
FROM information_schema.columns
WHERETABLE_SCHEMA='YourSchema'ANDTABLE_NAME='YourTable'ANDCOLUMN_NAME='YourField';

You can then just wrap this into a subquery, SELECT * FROM YourTable WHERE YourField = (queryabove). This lets you make a much more customizable, default-based list in your MySQL query.