Solved

The checking of MySQL field length

Posted on 2014-02-16
14
438 Views
Last Modified: 2014-02-17
Right now I willl have a function that use hte cursor to filter out the all column type and fields and then I will compare the field length one by one to see which one get extended by:

DECLARE fieldname CURSOR FOR 	
Select DISTINCT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE ,  Column_default, COLUMN_COMMENT, CHARACTER_MAXIMUM_LENGTH, 
CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, 
numeric_scale, substring(COLUMN_TYPE, 5, instr(COLUMN_TYPE, ')')-5) AS int_length
 FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = Trigger_databasename AND TABLE_NAME = trigger_tablename and COLUMN_NAME
NOT IN
(
Select DISTINCT COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'Audit_info' AND TABLE_NAME = CONCAT(Trigger_databasename,'_DBAudit_',trigger_tablename)
);

Open in new window


.
.
.
.

FETCH fieldname INTO current_COLUMN_NAME, current_COLUMN_TYPE, current_IS_NULLABLE, current_Column_default, current_COLUMN_COMMENT, 
   current_CHARACTER_MAXIMUM_LENGTH, current_CHARACTER_OCTET_LENGTH, current_NUMERIC_PRECISION, current_numeric_scale, current_int_length ;	
         IF fieldnameCursor_finished = 1 THEN 	
          LEAVE get_fieldlist;	
          END IF;	

Open in new window



So anyone knows if I check the column length by checking     current_CHARACTER_MAXIMUM_LENGTH, current_CHARACTER_OCTET_LENGTH, current_NUMERIC_PRECISION, current_numeric_scale, current_int_length, can they all compared directly or I have to convert all of them to a number BEFORE compare ?
0
Comment
Question by:marrowyung
  • 10
  • 4
14 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
so, your initial query is to find "new" fields (I presume, to add them to the audit table ...)

and the new/modifed query is to check "modified" fields (I presume to modify the audit table ... )

you have 2 options: write a new sql (suggested below), or combine the "new" with the current code into 1 with a return field which indicates if the field already existed, or if it was modified.
let's first try to find "modified" columns, the rest may come later
DECLARE fieldname CURSOR FOR 	
Select DISTINCT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE ,  Column_default, COLUMN_COMMENT, CHARACTER_MAXIMUM_LENGTH, 
CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, 
numeric_scale, substring(COLUMN_TYPE, 5, instr(COLUMN_TYPE, ')')-5) AS int_length
 FROM INFORMATION_SCHEMA.COLUMNS  t
WHERE TABLE_SCHEMA = Trigger_databasename AND TABLE_NAME = trigger_tablename AND EXISTS
(
Select NULL
 FROM INFORMATION_SCHEMA.COLUMNS  a
WHERE a.TABLE_SCHEMA = 'Audit_info' 
 AND a.TABLE_NAME = CONCAT(Trigger_databasename,'_DBAudit_',trigger_tablename)
  AND a.COLUMN_NAME = t.COLUMN_NAME
  AND ( a.CHARACTER_MAXIMUM_LENGTH <> t.CHARACTER_MAXIMUM_LENGTH
        OR a.CHARACTER_OCTET_LENGTH <> t.CHARACTER_OCTET_LENGTH
        OR a.NUMERIC_PRECISION <> t.NUMERIC_PRECISION
        OR a.numeric_scale <> t.NUMERIC_PRECISION
        OR substring(a.COLUMN_TYPE, 5, instr(a.COLUMN_TYPE, ')')-5) <> substring(t.COLUMN_TYPE, 5, instr(t.COLUMN_TYPE, ')')-5) 
         )
);

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"so, your initial query is to find "new" fields (I presume, to add them to the audit table ...)"

yes, but I have to modify it so that 2 x table with different name will have field compare head to head and see if the field LENGTH extended.

so when I do this:

FETCH fieldname INTO current_COLUMN_NAME, current_COLUMN_TYPE, current_IS_NULLABLE, current_Column_default, current_COLUMN_COMMENT, 
   current_CHARACTER_MAXIMUM_LENGTH, current_CHARACTER_OCTET_LENGTH, current_NUMERIC_PRECISION, current_numeric_scale, current_int_length ;	
         IF fieldnameCursor_finished = 1 THEN 

Open in new window


should I convert the current_int_length to number vaule before I can compare?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
why "Select NULL"?


this one:

WHERE a.TABLE_SCHEMA = 'Audit_info' 
 AND a.TABLE_NAME = CONCAT(Trigger_databasename,'_DBAudit_',trigger_tablename)
  AND a.COLUMN_NAME = t.COLUMN_NAME

Open in new window


means now I compare to the same table in other dB? which I think about it too but not combine into signal statement.

this one:

 AND ( a.CHARACTER_MAXIMUM_LENGTH <> t.CHARACTER_MAXIMUM_LENGTH
        OR a.CHARACTER_OCTET_LENGTH <> t.CHARACTER_OCTET_LENGTH
        OR a.NUMERIC_PRECISION <> t.NUMERIC_PRECISION
        OR a.numeric_scale <> t.NUMERIC_PRECISION
        OR substring(a.COLUMN_TYPE, 5, instr(a.COLUMN_TYPE, ')')-5) <> substring(t.COLUMN_TYPE, 5, instr(t.COLUMN_TYPE, ')')-5) 
         )

Open in new window


means any of the field of that rows has changed then return it to the cursor's table ?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
one more thing:

AND EXISTS

Open in new window


should there fields in between AND and EXISTS? MySQL might complain about the syntax as it is looking for what EXITS in the result set of subquery.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
first, let's sort out the syntax stuff about EXISTS():
http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I have just modify the field length of only one field but the query you gave me return a lot if field for me. but the one I changed is on the list.

now has to make the result set only return the field I changed .

any idea?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>now has to make the result set only return the field I changed .

well, the query will return all the fields for which there are differences, I must presume that your audit tables still have some differences, hence the query reports them ...

you could build a JOIN (instead of EXISTS)  to compare the data directly:
Select t.COLUMN_NAME, t.COLUMN_TYPE, t.IS_NULLABLE ,  t.Column_default, t.COLUMN_COMMENT
, t.CHARACTER_MAXIMUM_LENGTH, t.CHARACTER_OCTET_LENGTH, t.NUMERIC_PRECISION, t.numeric_scale, substring(t.COLUMN_TYPE, 5, instr(t.COLUMN_TYPE, ')')-5) AS int_length
, a.CHARACTER_MAXIMUM_LENGTH CHARACTER_MAXIMUM_LENGTH_audit, a.CHARACTER_OCTET_LENGTH CHARACTER_OCTET_LENGTH_audit, a.NUMERIC_PRECISION NUMERIC_PRECISION_audit, a.numeric_scale numeric_scale_audit, substring(a.COLUMN_TYPE, 5, instr(a.COLUMN_TYPE, ')')-5) AS int_length_audit

FROM INFORMATION_SCHEMA.COLUMNS  t
JOIN INFORMATION_SCHEMA.COLUMNS  a
    ON a.TABLE_SCHEMA = 'Audit_info' 
  AND a.TABLE_NAME = CONCAT(Trigger_databasename,'_DBAudit_',trigger_tablename)
  AND a.COLUMN_NAME = t.COLUMN_NAME
  AND ( a.CHARACTER_MAXIMUM_LENGTH <> t.CHARACTER_MAXIMUM_LENGTH
        OR a.CHARACTER_OCTET_LENGTH <> t.CHARACTER_OCTET_LENGTH
        OR a.NUMERIC_PRECISION <> t.NUMERIC_PRECISION
        OR a.numeric_scale <> t.NUMERIC_PRECISION
        OR substring(a.COLUMN_TYPE, 5, instr(a.COLUMN_TYPE, ')')-5) <> substring(t.COLUMN_TYPE, 5, instr(t.COLUMN_TYPE, ')')-5) 
         )

WHERE t.TABLE_SCHEMA = Trigger_databasename 
AND t.TABLE_NAME = trigger_tablename 

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"well, the query will return all the fields for which there are differences, I must presume that your audit tables still have some differences, hence the query reports them"

no, I just modify one field ! how can it report all ?

the new one you gave me return the same result set !
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I verify the field with same length on both table are also in the result set !! so something is not working good !
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I think I found out why:

your query match the wrong field:

      OR a.numeric_scale <> t.NUMERIC_PRECISION

Open in new window


it should be:


      OR a.numeric_scale <> t.numeric_scale 

Open in new window


same field, right !
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I just try to verify what if I roll back the change and the script should return nothing to me, I am right !
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
now I change the key condition to :

 AND ( a.CHARACTER_MAXIMUM_LENGTH < t.CHARACTER_MAXIMUM_LENGTH
        OR a.CHARACTER_OCTET_LENGTH < t.CHARACTER_OCTET_LENGTH
        OR a.NUMERIC_PRECISION < t.NUMERIC_PRECISION
        OR a.numeric_scale < t.numeric_scale 
        OR substring(a.COLUMN_TYPE, 5, instr(a.COLUMN_TYPE, ')')-5) < substring(t.COLUMN_TYPE, 5, instr(t.COLUMN_TYPE, ')')-5) 

Open in new window


I only need to take care the situation where length extended, not shortened ! this should be smart enought!
0
 
LVL 1

Author Closing Comment

by:marrowyung
Comment Utility
I am improving !
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
glad I could help, sorry of the bad sql before ...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now