SQL is a standard that would make our lives easier if it was implemented consistently across major database back-ends. Beyond simple queries it is rarely the case, and one example is table alteration. Today I was writing an abstraction over JDBC that will be used behind Datazenit Schema Builder, and I got reminded about those differences once again.
Postgres has a nice syntax to modify columns in a table. For example you can change each attribute separately.
MySQL is a bit more clumsy than Postgres and allows changing a column as only a whole. It means you must specify each attribute if you don’t want to lose existing column settings.
As you can see this introduces a lot of unnecessary paramaters to the query. You must also know all the attributes beforehand. Another thing you may have noticed is the difference between identifier quote symbols, but that’s a topic for another time.
How to get all settings for a column if MySQL requires them? There are several options, but an convenient way is a
SHOW FULL COLUMNS query.
The output will be similar to the table shown below except with even more info.
You can use this data to build the definition of column on the server side. For those that prefer a one-liner, StackOverflows provides a solution. It is a single MySQL query that returns the final column definition. Below is a little bit modified version that uses CONCAT_WS instead of CONCAT with manual spaces and includes all column parameters.
Examine and test before using any of this. I will stick to manipulating the data from server side, because my Schema Builder must be able to change all parameters not just the type, and writing a large query for each parameter would be an overkill. Also Scala, the back-end language of Datazenit, provides more flexibility and abstraction than SQL. Obviously.