To demonstrate this, say we have a database with two tables Artists and Albums. The GROUP_CONCAT() function can be useful for occasions where you want to provide a list of results, grouped by another column.įor example, you might want a list of artists, with each artist followed by a list of albums they’ve released. We also use the SEPARATOR argument to specify that the delimiter to be used between each (concatenated) row should be a space (instead of the default comma). In this example we return both the TaskId column and the TaskName column, separated by a closing parentheses and a space. | 1) Do garden 2) Feed cats 3) Paint roof 4) Take dog for walk 5) Relax 6) Feed cats | | GROUP_CONCAT(TaskId, ') ', TaskName SEPARATOR ' ') | You can also concatenate columns, and provide their own separator by providing a string literal value.Įxample: SELECT GROUP_CONCAT(TaskId, ') ', TaskName SEPARATOR ' ') | Do garden + Feed cats + Paint roof + Relax + Take dog for walk | | GROUP_CONCAT(DISTINCT TaskName SEPARATOR ' + ') | To do this, use SEPARATOR followed by the string literal value that should be inserted between group values.Įxample: SELECT GROUP_CONCAT(DISTINCT TaskName SEPARATOR ' + ') However, you can specify a delimiter of your choice if required. Example – Specify a Delimiterīy default, the list is a comma-separated list. The alternative (and default) value is ASC for ascending. So in this case I use DESC to specify that it should be in descending order. | Take dog for walk,Relax,Paint roof,Feed cats,Do garden | | GROUP_CONCAT(DISTINCT TaskName ORDER BY TaskName DESC) | You can use ORDER BY to order the results by a given column.Įxample: SELECT GROUP_CONCAT(DISTINCT TaskName ORDER BY TaskName DESC) So in this case, “Feed cats” is only listed once, whereas it was listed twice in the previous example. | Do garden,Feed cats,Paint roof,Relax,Take dog for walk | You can use DISTINCT to remove duplicates (so that duplicate records become one record).Įxample: SELECT GROUP_CONCAT(DISTINCT TaskName) Note that there are restrictions on how long this list can be. By default, the list is separated by a comma. | Do garden,Feed cats,Paint roof,Take dog for walk,Relax,Feed cats |Īs you can see, each row from the result set has been concatenated into a single row. Here’s a basic example to demonstrate the GROUP_CONCAT() function: SELECT GROUP_CONCAT(TaskName) The Dataįirst, let’s use the following data in our first few examples: USE Solutions This article provides examples of how it all works. The GROUP_CONCAT() function was built specifically for the purpose of concatenating a query’s result set into a list separated by either a comma, or a delimiter of your choice. Regards guys.In MySQL, you can return your query results as a comma separated list by using the GROUP_CONCAT() function. Where table_schema = 'db2' and table_name = 'table2' ) as i2Īnd the obvious result is dob that is present in table1 and not in table2. Where table_schema = 'db1' and table_name = 'table1' ) as i1 Id int not null auto_increment primary key, I add an example of two different databases on the same server. As you said your problem was quite different because it concerned with different servers. So, you can even compare fields between tables with a left join for example.Įdit. Information schema allows you to make queries on metadata. Where table_schema = 'database_name' and table_name = 'table_name'Įdit. Take a look at the information_lumns table select group_concat(column_name order by ordinal_position) The results are a list of any columns that exist in the first database and not in the second. WHERE table_schema = 'db_name' AND table_name = 'tbl_name' AND column_name NOT IN ('id', 'name', 'field1', 'field2') Then I can use this query to compare: SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) That gives me a list that looks like this: 'id', 'name', 'field1', 'field2' WHERE table_schema = 'db_name' AND table_name = 'tbl_name' Thanks for the answers, guys! Applying your answers to the question, this is my final SQL to get the column names: SELECT CONCAT("'", GROUP_CONCAT(column_name ORDER BY ordinal_position SEPARATOR "', '"), "'") AS columns What I am after is a comma-delimited list that I can then take to the live server and do: SHOW COLUMNS FROM table_name WHERE NOT IN ()Īny thoughts on how best to do this - either by correcting me in my own approach, or by another means all together? Obviously, the above SQL does not work.Ī note: The servers are entirely separate and may not communicate with each other, so no direct comparison is possible. I'd like to do something like this: SELECT GROUP_CONCAT(Field) FROM (SHOW COLUMNS ON table_name) GROUP BY Field I am comparing database tables on a development server against a live server, looking for column name changes, new columns, and columns that have been dropped.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |