All geek questions in one place

Determine the maximum length allowed in a column in mysql

The table structure looks like this:

registrant_id varchar(16) registrant_name varchar(128)

I want to run a query that displays all those records that correspond to the maximum resolving length, i.e. what I am currently doing for the above:

 SELECT * FROM `tm_registrant` WHERE length( `registrant_name` ) = 128

However, I don’t want to hardcode 128, because I have many columns, and I want to run one query to see all the records that have at least one fully filled column.

+3
source share
2 answers
 SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'your_table_name' AND table_schema = 'your_db_name' AND column_name = 'your_column_name' LIMIT 0 , 1
+6
source

you can use the combination of left, right and len to extract the length of the varchar fields using this command in your SQL status: SHOW FIELDS FROM tablename1 Where Field = 'registeristrant_name'

Or you can look at NFORMATION_SCHEMA.COLUMNS http://dev.mysql.com/doc/refman/5.1/en/columns-table.html

+1
source

Source: https://habr.com/ru/post/1210826/

More articles:


All Articles