Solved

The checking of MySQL field length

Posted on 2014-02-16
14
443 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
ID: 39863970
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
ID: 39864052
"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
ID: 39864063
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
ID: 39864069
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]
ID: 39864084
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
ID: 39864239
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]
ID: 39864269
>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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:marrowyung
ID: 39864367
"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
ID: 39864370
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
ID: 39864386
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
ID: 39864387
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
ID: 39864394
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
ID: 39864397
I am improving !
0
 
LVL 142

Expert Comment

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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Creating and Managing Databases with phpMyAdmin in cPanel.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

911 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

17 Experts available now in Live!

Get 1:1 Help Now