-
Feb
10thMySQL: How to Check if a Column Exists in a Table with SQL
To check if a column exists in a table with SQL using a MySQL database is pretty easy, thanks to the nifty SHOW COLUMNS command.To find if a specific column exists, we simply extend the standard SHOW COLUMNS FROM statement with a LIKE parameter, which allows us to search for a specific string or for a partial string using the standard % modifier. If the statement returns a non-empty result set, we know the column exists.
For example:
SHOW COLUMNS FROM `table` LIKE 'column-name';
will return a empty result set if your table doesn’t have a column named ‘column-name’ in it. Similarly,
SHOW COLUMNS FROM `table` LIKE 'column-name%';
will return a result set containing all columns that start with ‘column-name’, e.g. ‘column-name-a’ and ‘column-name-b’ as well as plain old ‘column-name’ of course!
Nifty.
- ‹ Review: BartenderBleach Cosplay: Soi Fon (Minami Tachibana) ›
Related Posts:
To check if a table exists with SQL in a MySQL database is pretty easy, thanks to the nifty SHOW TABLES command. To find if a specific table exists, we simply extend the standard SHOW TABLES statement with a LIKE parameter, which allows us to search for a specific string or for a partial string using the standar ...
If you make use of a datetime column in your MySQL database to store timestamp data, here is a simple way of adding a couple of hours to your already stored value. You know, if the client suddenly decided to change time zone or something like that! ;) UPDATE `table` SET `datetime-column` = DATE_ADD(`datetime-col ...
Identifying column values in your database that are currently set to NULL and then updating them to a default value is made easy through the use of the common UPDATE and not so common IFNULL function in MySQL. If say for example you have a legacy `color` column that used to allow NULL entries in your database, b ...
I tend to use a lot of tinyint columns as controllers for my database-held objects, usually sticking to the convention of 1 means on and 0 means off. In other words, the perfect target for a SQL toggle statement! In order to toggle update a value in SQL, we'll need to make use of the common SQL control flow func ...
To get a list of user accounts on a MySQL server instance, we need to make a SELECT call to the mysql.user table. First, fire up MySQL in your terminal and login as an administrator account with: mysql -u root -p You will be prompted for your root password before being granted access to the MySQL monitor. ...
