MySQL Cookbook

MySQL MySQL,database,server,cookbook MySQL Cookbook 1 Preface 2 What's in This Book 6 Chapter 1. Using the mysql Client Program 15 1.1 Introduction 17 1.2 Setting Up a MySQL User Account 18 1.2.1 Problem 18 1.2.2 Solution 18 1.2.3 Discussion 18 1.3 Creating a Database and a Sample Table 19 1.3.1 Problem 19 1.3.2 Solution 19 1.3.3 Discussion 19 1.4 Starting and Terminating mysql 21 1.4.1 Problem 21 1.4.2 Solution 21 1.4.3 Discussion 21 1.5 Specifying Connection Parameters by Using Option Files 23 1.5.1 Problem 23 1.5.2 Solution 23 1.5.3 Discussion 23 1.6 Protecting Option Files 25 1.6.1 Problem 25 1.6.2 Solution 25 1.6.3 Discussion 25 1.7 Mixing Command-Line and Option File Parameters 25 1.7.1 Problem 25 1.7.2 Solution 25 1.7.3 Discussion 25 1.8 What to Do if mysql Cannot Be Found 26 1.8.1 Problem 26 1.8.2 Solution 26 1.8.3 Discussion 26 1.9 Setting Environment Variables 28 1.9.1 Problem 28 1.9.2 Solution 28 1.9.3 Discussion 28 1.10 Issuing Queries 30 1.10.1 Problem 30 1.10.2 Solution 30 1.10.3 Discussion 30 1.11 Selecting a Database 31 1.11.1 Problem 31 1.11.2 Solution 31 1.11.3 Discussion 31 1.12 Canceling a Partially Entered Query 33 1.12.1 Problem 33 1.12.2 Solution 33 1.12.3 Discussion 33 1.13 Repeating and Editing Queries 33 1.13.1 Problem 33 1.13.2 Solution 34 1.13.3 Discussion 34 1.14 Using Auto-Completion for Database and Table Names 35 1.14.1 Problem 35 1.14.2 Solution 35 1.14.3 Discussion 35 1.15 Using SQL Variables in Queries 36 1.15.1 Problem 36 1.15.2 Solution 36 1.15.3 Discussion 36 1.16 Telling mysql to Read Queries from a File 38 1.16.1 Problem 38 1.16.2 Solution 38 1.16.3 Discussion 38 1.17 Telling mysql to Read Queries from Other Programs 41 1.17.1 Problem 41 1.17.2 Solution 41 1.17.3 Discussion 41 1.17.4 See Also 42 1.18 Specifying Queries on the Command Line 42 1.18.1 Problem 42 1.18.2 Solution 42 1.18.3 Discussion 42 1.18.4 See Also 43 1.19 Using Copy and Paste as a mysql Input Source 43 1.19.1 Problem 43 1.19.2 Solution 43 1.19.3 Discussion 43 1.20 Preventing Query Output from Scrolling off the Screen 43 1.20.1 Problem 43 1.20.2 Solution 43 1.20.3 Discussion 43 1.21 Sending Query Output to a File or to a Program 44 1.21.1 Problem 44 1.21.2 Solution 44 1.21.3 Discussion 45 1.22 Selecting Tabular or Tab-Delimited Query Output Format 46 1.22.1 Problem 46 1.22.2 Solution 46 1.22.3 Discussion 46 1.23 Specifying Arbitrary Output Column Delimiters 46 1.23.1 Problem 46 1.23.2 Solution 46 1.23.3 Discussion 47 1.23.4 See Also 48 1.24 Producing HTML Output 48 1.24.1 Problem 48 1.24.2 Solution 48 1.24.3 Discussion 48 1.25 Producing XML Output 49 1.25.1 Problem 49 1.25.2 Solution 49 1.25.3 Discussion 49 1.26 Suppressing Column Headings in Query Output 50 1.26.1 Problem 50 1.26.2 Solution 50 1.26.3 Discussion 50 1.27 Numbering Query Output Lines 51 1.27.1 Problem 51 1.27.2 Solution 51 1.27.3 Discussion 51 1.28 Making Long Output Lines More Readable 52 1.28.1 Problem 52 1.28.2 Solution 52 1.28.3 Discussion 52 1.29 Controlling mysql's Verbosity Level 53 1.29.1 Problem 53 1.29.2 Solution 53 1.29.3 Discussion 53 1.30 Logging Interactive mysql Sessions 53 1.30.1 Problem 53 1.30.2 Solution 54 1.30.3 Discussion 54 1.31 Creating mysql Scripts from Previously Executed Queries 54 1.31.1 Problem 54 1.31.2 Solution 55 1.31.3 Discussion 55 1.32 Using mysql as a Calculator 55 1.32.1 Problem 55 1.32.2 Solution 55 1.32.3 Discussion 55 1.33 Using mysql in Shell Scripts 56 1.33.1 Problem 56 1.33.2 Solution 56 1.33.3 Discussion 57 1.33.4 Writing Shell Scripts Under Unix 58 1.33.5 Writing Shell Scripts Under Windows 61 Chapter 2. Writing MySQL-Based Programs 63 2.1 Introduction 64 2.1.1 MySQL Client Application Programming Interfaces 64 2.1.2 Assumptions 68 2.2 Connecting to the MySQL Server, Selecting a Database, and Disconnecting 69 2.2.1 Problem 69 2.2.2 Solution 69 2.2.3 Discussion 69 2.2.4 Perl 70 2.2.4.1 Additional connection parameters 73 2.2.5 PHP 73 2.2.5.1 Additional connection parameters 75 2.2.6 Python 76 2.2.6.1 Additional connection parameters 77 2.2.7 Java 78 2.2.7.1 Additional connection parameters 82 2.3 Checking for Errors 82 2.3.1 Problem 82 2.3.2 Solution 83 2.3.3 Discussion 83 2.3.4 Perl 83 2.3.5 PHP 86 2.3.6 Python 88 2.3.7 Java 88 2.4 Writing Library Files 90 2.4.1 Problem 90 2.4.2 Solution 90 2.4.3 Discussion 90 2.4.4 Perl 93 2.4.5 PHP 95 2.4.6 Python 97 2.4.7 Java 99 2.5 Issuing Queries and Retrieving Results 102 2.5.1 Problem 102 2.5.2 Solution 102 2.5.3 Discussion 102 2.5.4 SQL Statement Categories 104 2.5.5 Perl 105 2.5.6 PHP 110 2.5.7 Python 113 2.5.8 Java 115 2.6 Moving Around Within a Result Set 118 2.6.1 Problem 118 2.6.2 Solution 118 2.6.3 Discussion 118 2.7 Using Prepared Statements and Placeholders in Queries 119 2.7.1 Problem 119 2.7.2 Solution 119 2.7.3 Discussion 119 2.7.4 Perl 120 2.7.5 PHP 122 2.7.6 Python 122 2.7.7 Java 123 2.8 Including Special Characters and NULL Values in Queries 124 2.8.1 Problem 124 2.8.2 Solution 124 2.8.3 Discussion 124 2.8.4 Perl 126 2.8.5 PHP 127 2.8.6 Python 129 2.8.7 Java 130 2.9 Handling NULL Values in Result Sets 132 2.9.1 Problem 132 2.9.2 Solution 132 2.9.3 Discussion 132 2.9.4 Perl 133 2.9.5 PHP 134 2.9.6 Python 135 2.9.7 Java 135 2.10 Writing an Object-Oriented MySQL Interface for PHP 136 2.10.1 Problem 136 2.10.2 Solution 136 2.10.3 Discussion 136 2.10.4 Class Overview 139 2.10.5 Connecting and Disconnecting 142 2.10.6 Error Handling 144 2.10.7 Issuing Queries and Processing the Results 145 2.10.8 Quoting and Placeholder Support 147 2.11 Ways of Obtaining Connection Parameters 150 2.11.1 Problem 150 2.11.2 Solution 150 2.11.3 Discussion 150 2.11.4 Getting Parameters from the Command Line 152 2.11.4.1 Perl 152 2.11.4.2 PHP 154 2.11.4.3 Python 154 2.11.4.4 Java 156 2.11.5 Getting Parameters from Option Files 159 2.11.5.1 Perl 160 2.11.5.2 PHP 161 2.11.5.3 Python 162 2.11.5.4 Java 163 2.12 Conclusion and Words of Advice 165 Chapter 3. Record Selection Techniques 166 3.1 Introduction 168 3.2 Specifying Which Columns to Display 169 3.2.1 Problem 169 3.2.2 Solution 169 3.2.3 Discussion 169 3.3 Avoiding Output Column Order Problems When Writing Programs 171 3.3.1 Problem 171 3.3.2 Solution 171 3.3.3 Discussion 171 3.4 Giving Names to Output Columns 171 3.4.1 Problem 171 3.4.2 Solution 172 3.4.3 Discussion 172 3.5 Using Column Aliases to Make Programs Easier to Write 174 3.5.1 Problem 174 3.5.2 Solution 174 3.5.3 Discussion 174 3.6 Combining Columns to Construct Composite Values 175 3.6.1 Problem 175 3.6.2 Solution 175 3.6.3 Discussion 175 3.7 Specifying Which Rows to Select 176 3.7.1 Problem 176 3.7.2 Solution 176 3.7.3 Discussion 176 3.8 WHERE Clauses and Column Aliases 179 3.8.1 Problem 179 3.8.2 Solution 179 3.8.3 Discussion 179 3.9 Displaying Comparisons to Find Out How Something Works 180 3.9.1 Problem 180 3.9.2 Solution 180 3.9.3 Discussion 180 3.10 Reversing or Negating Query Conditions 181 3.10.1 Problem 181 3.10.2 Solution 181 3.10.3 Discussion 181 3.10.4 See Also 182 3.11 Removing Duplicate Rows 182 3.11.1 Problem 182 3.11.2 Solution 182 3.11.3 Discussion 183 3.11.4 See Also 184 3.12 Working with NULL Values 184 3.12.1 Problem 184 3.12.2 Solution 184 3.12.3 Discussion 184 3.12.4 See Also 185 3.13 Negating a Condition on a Column That Contains NULL Values 185 3.13.1 Problem 185 3.13.2 Solution 185 3.13.3 Discussion 185 3.14 Writing Comparisons Involving NULL in Programs 186 3.14.1 Problem 186 3.14.2 Solution 187 3.14.3 Discussion 187 3.15 Mapping NULL Values to Other Values for Display 187 3.15.1 Problem 187 3.15.2 Solution 187 3.15.3 Discussion 188 3.16 Sorting a Result Set 189 3.16.1 Problem 189 3.16.2 Solution 189 3.16.3 Discussion 189 3.17 Selecting Records from the Beginning or End of a Result Set 190 3.17.1 Problem 190 3.17.2 Solution 190 3.17.3 Discussion 190 3.17.4 See Also 192 3.18 Pulling a Section from the Middle of a Result Set 193 3.18.1 Problem 193 3.18.2 Solution 193 3.18.3 Discussion 193 3.19 Choosing Appropriate LIMIT Values 194 3.19.1 Problem 194 3.19.2 Solution 194 3.19.3 Discussion 194 3.20 Calculating LIMIT Values from Expressions 197 3.20.1 Problem 197 3.20.2 Solution 197 3.20.3 Discussion 197 3.21 What to Do When LIMIT Requires the "Wrong" Sort Order 197 3.21.1 Problem 197 3.21.2 Solution 198 3.21.3 Discussion 198 3.22 Selecting a Result Set into an Existing Table 199 3.22.1 Problem 199 3.22.2 Solution 199 3.22.3 Discussion 199 3.23 Creating a Destination Table on the Fly from a Result Set 201 3.23.1 Problem 201 3.23.2 Solution 201 3.23.3 Discussion 201 3.23 Creating a Destination Table on the Fly from a Result Set 201 3.23.1 Problem 201 3.23.2 Solution 201 3.23.3 Discussion 201 3.24 Moving Records Between Tables Safely 203 3.24.1 Problem 203 3.24.2 Solution 203 3.24.3 Discussion 203 3.25 Creating Temporary Tables 204 3.25.1 Problem 204 3.25.2 Solution 204 3.25.3 Discussion 204 3.26 Cloning a Table Exactly 206 3.26.1 Problem 206 3.26.2 Solution 206 3.26.3 Discussion 206 3.27 Generating Unique Table Names 208 3.27.1 Problem 208 3.27.2 Solution 208 3.27.3 Discussion 208 Chapter 4. Working with Strings 210 4.1 Introduction 211 4.1.1 Types of Strings 211 4.2 Writing Strings That Include Quotes or Special Characters 212 4.2.1 Problem 212 4.2.2 Solution 212 4.2.3 Discussion 212 4.2.4 See Also 214 4.3 Preserving Trailing Spaces in String Columns 214 4.3.1 Problem 214 4.3.2 Solution 214 4.3.3 Discussion 214 4.4 Testing String Equality or Relative Ordering 215 4.4.1 Problem 215 4.4.2 Solution 215 4.4.3 Discussion 215 4.4.4 See Also 216 4.5 Decomposing or Combining Strings 216 4.5.1 Problem 216 4.5.2 Solution 216 4.5.3 Discussion 216 4.6 Checking Whether a String Contains a Substring 219 4.6.1 Problem 219 4.6.2 Solution 219 4.6.3 Discussion 219 4.7 Pattern Matching with SQL Patterns 220 4.7.1 Problem 220 4.7.2 Solution 220 4.7.3 Discussion 220 4.8 Pattern Matching with Regular Expressions 222 4.8.1 Problem 222 4.8.2 Solution 222 4.8.3 Discussion 222 4.9 Matching Pattern Metacharacters Literally 227 4.9.1 Problem 227 4.9.2 Solution 227 4.9.3 Discussion 227 4.10 Controlling Case Sensitivity in String Comparisons 229 4.10.1 Problem 229 4.10.2 Solution 229 4.10.3 Discussion 230 4.11 Controlling Case Sensitivity in Pattern Matching 233 4.11.1 Problem 233 4.11.2 Solution 233 4.11.3 Discussion 233 4.12 Using FULLTEXT Searches 236 4.12.1 Problem 236 4.12.2 Solution 236 4.12.3 Discussion 236 4.12.4 See Also 240 4.13 Using a FULLTEXT Search with Short Words 241 4.13.1 Problem 241 4.13.2 Solution 241 4.13.3 Discussion 241 4.14 Requiring or Excluding FULLTEXT Search Words 242 4.14.1 Problem 242 4.14.2 Solution 242 4.14.3 Discussion 243 4.15 Performing Phrase Searches with a FULLTEXT Index 244 4.15.1 Problem 244 4.15.2 Solution 244 4.15.3 Discussion 244 Chapter 5. Working with Dates and Times 247 5.1 Introduction 249 5.1.1 MySQL's Date and Time Formats 250 5.2 Changing MySQL's Date Format 251 5.2.1 Problem 251 5.2.2 Solution 251 5.2.3 Discussion 252 5.3 Telling MySQL How to Display Dates or Times 252 5.3.1 Problem 252 5.3.2 Solution 252 5.3.3 Discussion 253 5.4 Determining the Current Date or Time 254 5.4.1 Problem 254 5.4.2 Solution 255 5.4.3 Discussion 255 5.5 Decomposing Dates and Times Using Formatting Functions 255 5.5.1 Problem 255 5.5.2 Solution 256 5.5.3 Discussion 256 5.5.4 See Also 257 5.6 Decomposing Dates or Times Using Component-Extraction Functions 257 5.6.1 Problem 257 5.6.2 Solution 257 5.6.3 Discussion 257 5.6.4 See Also 260 5.7 Decomposing Dates or Times Using String Functions 260 5.7.1 Problem 260 5.7.2 Solution 260 5.7.3 Discussion 260 5.8 Synthesizing Dates or Times Using Formatting Functions 262 5.8.1 Problem 262 5.8.2 Solution 262 5.8.3 Discussion 262 5.9 Synthesizing Dates or Times Using Component-Extraction Functions 263 5.9.1 Problem 263 5.9.2 Solution 263 5.9.3 Discussion 263 5.10 Combining a Date and a Time into a Date-and-Time Value 264 5.10.1 Problem 264 5.10.2 Solution 264 5.10.3 Discussion 264 5.11 Converting Between Times and Seconds 264 5.11.1 Problem 264 5.11.2 Solution 264 5.12 Converting Between Dates and Days 266 5.12.1 Problem 266 5.12.2 Solution 266 5.12.3 Discussion 266 5.13 Converting Between Date-and-Time Values and Seconds 267 5.13.1 Problem 267 5.13.2 Solution 267 5.13.3 Discussion 268 5.14 Adding a Temporal Interval to a Time 269 5.14.1 Problem 269 5.14.2 Solution 269 5.14.3 Discussion 269 5.15 Calculating Intervals Between Times 270 5.15.1 Problem 270 5.15.2 Solution 270 5.15.3 Discussion 270 5.16 Breaking Down Time Intervals into Components 271 5.16.1 Problem 271 5.16.2 Solution 271 5.16.3 Discussion 271 5.17 Adding a Temporal Interval to a Date 272 5.17.1 Problem 272 5.17.2 Solution 272 5.17.3 Discussion 273 5.18 Calculating Intervals Between Dates 275 5.18.1 Problem 275 5.18.2 Solution 275 5.18.3 Discussion 275 5.19 Canonizing Not-Quite-ISO Date Strings 278 5.19.1 Problem 278 5.19.2 Solution 278 5.19.3 Discussion 278 5.20 Calculating Ages 279 5.20.1 Problem 279 5.20.2 Solution 279 5.20.3 Discussion 279 5.20.4 Determining Ages in Years 280 5.20.5 Determining Ages in Months 282 5.21 Shifting Dates by a Known Amount 283 5.21.1 Problem 283 5.21.2 Solution 283 5.21.3 Discussion 283 5.21.4 Calculating Anniversary Dates 283 5.21.5 Time Zone Adjustments 284 5.22 Finding First and Last Days of Months 284 5.22.1 Problem 284 5.22.2 Solution 284 5.22.3 Discussion 284 5.23 Finding the Length of a Month 287 5.23.1 Problem 287 5.23.2 Solution 287 5.23.3 Discussion 287 5.23.4 See Also 287 5.24 Calculating One Date from Another by Substring Replacement 288 5.24.1 Problem 288 5.24.2 Solution 288 5.24.3 Discussion 288 5.25 Finding the Day of the Week for a Date 289 5.25.1 Problem 289 5.25.2 Solution 289 5.25.3 Discussion 289 5.26 Finding Dates for Days of the Current Week 290 5.26.1 Problem 290 5.26.2 Solution 290 5.26.3 Discussion 290 5.27 Finding Dates for Weekdays of Other Weeks 291 5.27.1 Problem 291 5.27.2 Solution 291 5.27.3 Discussion 291 5.28 Performing Leap Year Calculations 293 5.28.1 Problem 293 5.28.2 Solution 293 5.28.3 Discussion 293 5.28.4 Determining Whether a Date Occurs in a Leap Year 293 5.28.5 Using Leap Year Tests for Year-Length Calculations 295 5.28.6 Using Leap Year Tests for Month-Length Calculations 295 5.29 Treating Dates or Times as Numbers 296 5.29.1 Problem 296 5.29.2 Solution 296 5.29.3 Discussion 296 5.30 Forcing MySQL to Treat Strings as Temporal Values 297 5.30.1 Problem 297 5.30.2 Solution 297 5.30.3 Discussion 297 5.31 Selecting Records Based on Their Temporal Characteristics 298 5.31.1 Problem 298 5.31.2 Solution 298 5.31.3 Discussion 298 5.31.4 Comparing Dates to One Another 299 5.31.5 Comparing Times to One Another 301 5.31.6 Comparing Dates to Calendar Days 301 5.32 Using TIMESTAMP Values 302 5.32.1 Problem 302 5.32.2 Solution 302 5.32.3 Discussion 302 5.33 Recording a Row's Last Modification Time 303 5.33.1 Problem 303 5.33.2 Solution 303 5.33.3 Discussion 303 5.34 Recording a Row's Creation Time 305 5.34.1 Problem 305 5.34.2 Solution 305 5.34.3 Discussion 305 5.35 Performing Calculations with TIMESTAMP Values 306 5.35.1 Problem 306 5.35.2 Solution 306 5.35.3 Discussion 306 5.36 Displaying TIMESTAMP Values in Readable Form 307 5.36.1 Problem 307 5.36.2 Solution 307 5.36.3 Discussion 307 Chapter 6. Sorting Query Results 309 6.1 Introduction 310 6.2 Using ORDER BY to Sort Query Results 311 6.2.1 Problem 311 6.2.2 Solution 311 6.2.3 Discussion 311 6.2.4 Naming the Sort Columns and Specifying Sorting Direction 312 6.2.5 More Ways to Refer to Sort Columns 314 6.3 Sorting Subsets of a Table 315 6.3.1 Problem 315 6.3.2 Solution 315 6.3.3 Discussion 316 6.4 Sorting Expression Results 316 6.4.1 Problem 316 6.4.2 Solution 316 6.4.3 Discussion 316 6.5 Displaying One Set of Values While Sorting by Another 318 6.5.1 Problem 318 6.5.2 Solution 318 6.5.3 Discussion 318 6.6 Sorting and NULL Values 323 6.6.1 Problem 323 6.6.2 Solution 323 6.6.3 Discussion 323 6.7 Controlling Case Sensitivity of String Sorts 325 6.7.1 Problem 325 6.7.2 Solution 325 6.7.3 Discussion 325 6.8 Date-Based Sorting 327 6.8.1 Problem 327 6.8.2 Solution 327 6.8.3 Discussion 327 6.9 Sorting by Calendar Day 328 6.9.1 Problem 328 6.9.2 Solution 328 6.9.3 Discussion 328 6.10 Sorting by Day of Week 331 6.10.1 Problem 331 6.10.2 Solution 331 6.10.3 Discussion 331 6.11 Sorting by Time of Day 332 6.11.1 Problem 332 6.11.2 Solution 332 6.11.3 Discussion 332 6.12 Sorting Using Substrings of Column Values 333 6.12.1 Problem 333 6.12.2 Solution 333 6.12.3 Discussion 333 6.13 Sorting by Fixed-Length Substrings 334 6.13.1 Problem 334 6.13.2 Solution 334 6.13.3 Discussion 334 6.14 Sorting by Variable-Length Substrings 336 6.14.1 Problem 336 6.14.2 Solution 336 6.14.3 Discussion 336 6.15 Sorting Hostnames in Domain Order 341 6.15.1 Problem 341 6.15.2 Solution 341 6.15.3 Discussion 341 6.16 Sorting Dotted-Quad IP Values in Numeric Order 343 6.16.1 Problem 343 6.16.2 Solution 343 6.16.3 Discussion 343 6.17 Floating Specific Values to the Head or Tail of the Sort Order 345 6.17.1 Problem 345 6.17.2 Solution 345 6.17.3 Discussion 345 6.18 Sorting in User-Defined Orders 347 6.18.1 Problem 347 6.18.2 Solution 347 6.18.3 Discussion 347 6.19 Sorting ENUM Values 348 6.19.1 Problem 348 6.19.2 Solution 348 6.19.3 Discussion 349 Chapter 7. Generating Summaries 352 7.1 Introduction 353 7.2 Summarizing with COUNT( ) 354 7.2.1 Problem 354 7.2.2 Solution 354 7.2.3 Discussion 355 7.2.4 See Also 357 7.3 Summarizing with MIN( ) and MAX( ) 357 7.3.1 Problem 357 7.3.2 Solution 357 7.3.3 Discussion 357 7.4 Summarizing with SUM( ) and AVG( ) 359 7.4.1 Problem 359 7.4.2 Solution 359 7.4.3 Discussion 359 7.4.4 See Also 360 7.5 Using DISTINCT to Eliminate Duplicates 360 7.5.1 Problem 360 7.5.2 Solution 360 7.5.3 Discussion 360 7.6 Finding Values Associated with Minimum and Maximum Values 363 7.6.1 Problem 363 7.6.2 Solution 363 7.6.3 Discussion 363 7.6.4 See Also 366 7.7 Controlling String Case Sensitivity for MIN( ) and MAX( ) 366 7.7.1 Problem 366 7.7.2 Solution 366 7.7.3 Discussion 366 7.8 Dividing a Summary into Subgroups 367 7.8.1 Problem 367 7.8.2 Solution 368 7.8.3 Discussion 368 7.9 Summaries and NULL Values 372 7.9.1 Problem 372 7.9.2 Solution 372 7.9.3 Discussion 372 7.10 Selecting Only Groups with Certain Characteristics 375 7.10.1 Problem 375 7.10.2 Solution 375 7.10.3 Discussion 375 7.11 Determining Whether Values are Unique 376 7.11.1 Problem 376 7.11.2 Solution 376 7.11.3 Discussion 376 7.12 Grouping by Expression Results 377 7.12.1 Problem 377 7.12.2 Solution 377 7.12.3 Discussion 377 7.13 Categorizing Non-Categorical Data 379 7.13.1 Problem 379 7.13.2 Solution 379 7.13.3 Discussion 379 7.14 Controlling Summary Display Order 382 7.14.1 Problem 382 7.14.2 Solution 382 7.14.3 Discussion 382 7.15 Finding Smallest or Largest Summary Values 384 7.15.1 Problem 384 7.15.2 Solution 384 7.15.3 Discussion 384 7.16 Date-Based Summaries 386 7.16.1 Problem 386 7.16.2 Solution 386 7.16.3 Discussion 386 7.17 Working with Per-Group and Overall Summary Values Simultaneously 389 7.17.1 Problem 389 7.17.2 Solution 389 7.17.3 Discussion 389 7.18 Generating a Report That Includes a Summary and a List 391 7.18.1 Problem 391 7.18.2 Solution 392 7.18.3 Discussion 392 Chapter 8. Modifying Tables with ALTER TABLE 395 8.1 Introduction 396 8.2 Dropping, Adding, or Repositioning a Column 397 8.2.1 Problem 397 8.2.2 Solution 397 8.2.3 Discussion 397 8.3 Changing a Column Definition or Name 399 8.3.1 Problem 399 8.3.2 Solution 399 8.3.3 Discussion 399 8.4 The Effect of ALTER TABLE on Null and Default Value Attributes 401 8.4.1 Problem 401 8.4.2 Solution 401 8.4.3 Discussion 401 8.5 Changing a Column's Default Value 403 8.5.1 Problem 403 8.5.2 Solution 403 8.5.3 Discussion 403 8.6 Changing a Table Type 403 8.6.1 Problem 403 8.6.2 Solution 403 8.6.3 Discussion 403 8.7 Renaming a Table 405 8.7.1 Problem 405 8.7.2 Solution 405 8.7.3 Discussion 405 8.8 Adding or Dropping Indexes 406 8.8.1 Problem 406 8.8.2 Solution 406 8.8.3 Discussion 406 8.8.4 Adding Indexes 407 8.8.5 Dropping Indexes 408 8.8.6 See Also 409 8.9 Eliminating Duplicates by Adding an Index 409 8.9.1 Problem 409 8.9.2 Solution 409 8.9.3 Discussion 409 8.10 Using ALTER TABLE to Normalize a Table 410 8.10.1 Problem 410 8.10.2 Solution 410 8.10.3 Discussion 410 Chapter 9. Obtaining and Using Metadata 417 9.1 Introduction 418 9.2 Obtaining the Number of Rows Affected by a Query 419 9.2.1 Problem 419 9.2.2 Solution 419 9.2.3 Discussion 419 9.2.4 Perl 420 9.2.5 PHP 420 9.2.6 Python 420 9.2.7 Java 421 9.3 Obtaining Result Set Metadata 421 9.3.1 Problem 421 9.3.2 Solution 421 9.3.3 Discussion 421 9.3.4 Perl 422 9.3.5 PHP 425 9.3.6 Python 426 9.3.7 Java 427 9.4 Determining Presence or Absence of a Result Set 429 9.4.1 Problem 429 9.4.2 Solution 429 9.4.3 Discussion 429 9.5 Formatting Query Results for Display 430 9.5.1 Problem 430 9.5.2 Solution 430 9.5.3 Discussion 430 9.6 Getting Table Structure Information 433 9.6.1 Problem 433 9.6.2 Solution 433 9.6.3 Discussion 433 9.6.4 Using SHOW COLUMNS to Get Table Structure 434 9.6.5 Using Result Set Metadata to Get Table Structure 438 9.6.6 Using CREATE TABLE to Get Table Structure 440 9.7 Getting ENUM and SET Column Information 441 9.7.1 Problem 441 9.7.2 Solution 441 9.7.3 Discussion 441 9.8 Database-Independent Methods of Obtaining Table Information 443 9.8.1 Problem 443 9.8.2 Solution 443 9.8.3 Discussion 443 9.9 Applying Table Structure Information 445 9.9.1 Problem 445 9.9.2 Solution 445 9.9.3 Discussion 445 9.9.4 Displaying Column Lists 445 9.9.5 Interactive Record Editing 445 9.9.6 Mapping Column Types onto Web Page Elements 446 9.9.7 Adding Elements to ENUM or SET Column Definitions 446 9.9.8 Retrieving Dates in Non-ISO Format 449 9.9.9 Converting Character Columns Between Fixed-Length and Variable-Length Types 449 9.9.10 Selecting All Except Certain Columns 451 9.10 Listing Tables and Databases 452 9.10.1 Problem 452 9.10.2 Solution 452 9.10.3 Discussion 452 9.11 Testing Whether a Table Exists 453 9.11.1 Problem 453 9.11.2 Solution 453 9.11.3 Discussion 453 9.12 Testing Whether a Database Exists 454 9.12.1 Problem 454 9.12.2 Solution 454 9.12.3 Discussion 454 9.13 Getting Server Metadata 455 9.13.1 Problem 455 9.13.2 Solution 455 9.13.3 Discussion 455 9.14 Writing Applications That Adapt to the MySQL Server Version 455 9.14.1 Problem 455 9.14.2 Solution 455 9.14.3 Discussion 456 9.15 Determining the Current Database 457 9.15.1 Problem 457 9.15.2 Solution 457 9.15.3 Discussion 457 9.16 Determining the Current MySQL User 457 9.16.1 Problem 457 9.16.2 Solution 458 9.16.3 Discussion 458 9.17 Monitoring the MySQL Server 459 9.17.1 Problem 459 9.17.2 Solution 459 9.17.3 Discussion 459 9.18 Determining Which Table Types the Server Supports 460 9.18.1 Problem 460 9.18.2 Solution 460 9.18.3 Discussion 460 Chapter 10. Importing and Exporting Data 463 10.1 Introduction 465 10.1.1 General Import and Export Issues 466 10.1.2 File Formats 467 10.1.3 Notes on Invoking Shell Commands 468 10.2 Importing Data with LOAD DATA and mysqlimport 469 10.2.1 Problem 469 10.2.2 Solution 469 10.2.3 Discussion 469 10.3 Specifying the Datafile Location 470 10.3.1 Problem 470 10.3.2 Solution 470 10.3.3 Discussion 471 10.4 Specifying the Datafile Format 473 10.4.1 Problem 473 10.4.2 Solution 473 10.4.3 Discussion 473 10.5 Dealing with Quotes and Special Characters 474 10.5.1 Problem 474 10.5.2 Solution 474 10.5.3 Discussion 474 10.6 Importing CSV Files 475 10.6.1 Problem 475 10.6.2 Solution 475 10.6.3 Discussion 475 10.7 Reading Files from Different Operating Systems 476 10.7.1 Problem 476 10.7.2 Solution 476 10.7.3 Discussion 476 10.8 Handling Duplicate Index Values 476 10.8.1 Problem 476 10.8.2 Solution 477 10.8.3 Discussion 477 10.9 Getting LOAD DATA to Cough Up More Information 477 10.9.1 Problem 477 10.9.2 Solution 477 10.9.3 Discussion 477 10.10 Don't Assume LOAD DATA Knows More than It Does 478 10.10.1 Problem 478 10.10.2 Solution 478 10.10.3 Discussion 478 10.11 Skipping Datafile Lines 479 10.11.1 Problem 479 10.11.2 Solution 479 10.11.3 Discussion 479 10.12 Specifying Input Column Order 480 10.12.1 Problem 480 10.12.2 Solution 480 10.12.3 Discussion 480 10.13 Skipping Datafile Columns 481 10.13.1 Problem 481 10.13.2 Solution 481 10.13.3 Discussion 481 10.13.4 See Also 482 10.14 Exporting Query Results from MySQL 482 10.14.1 Problem 482 10.14.2 Solution 482 10.14.3 Discussion 482 10.14.4 Exporting with the SELECT ... INTO OUTFILE Statement 482 10.14.5 Using the mysql Client to Export Data 483 10.14.6 See Also 484 10.15 Exporting Tables as Raw Data 484 10.15.1 Problem 484 10.15.2 Solution 484 10.15.3 Discussion 484 10.16 Exporting Table Contents or Definitions in SQL Format 485 10.16.1 Problem 485 10.16.2 Solution 485 10.16.3 Discussion 486 10.17 Copying Tables or Databases to Another Server 488 10.17.1 Problem 488 10.17.2 Solution 488 10.17.3 Discussion 488 10.18 Writing Your Own Export Programs 489 10.18.1 Problem 489 10.18.2 Solution 489 10.18.3 Discussion 489 10.19 Converting Datafiles from One Format to Another 493 10.19.1 Problem 493 10.19.2 Solution 493 10.19.3 Discussion 493 10.20 Extracting and Rearranging Datafile Columns 495 10.20.1 Problem 495 10.20.2 Solution 495 10.20.3 Discussion 495 10.21 Validating and Transforming Data 498 10.21.1 Problem 498 10.21.2 Solution 498 10.21.3 Discussion 498 10.21.4 Writing an Input-Processing Loop 499 10.21.5 Putting Common Tests in Libraries 500 10.22 Validation by Direct Comparison 501 10.22.1 Problem 501 10.22.2 Solution 501 10.22.3 Discussion 501 10.23 Validation by Pattern Matching 501 10.23.1 Problem 501 10.23.2 Solution 502 10.23.3 Discussion 502 10.24 Using Patterns to Match Broad Content Types 504 10.24.1 Problem 504 10.24.2 Solution 505 10.24.3 Discussion 505 10.25 Using Patterns to Match Numeric Values 505 10.25.1 Problem 505 10.25.2 Solution 505 10.25.3 Discussion 505 10.26 Using Patterns to Match Dates or Times 507 10.26.1 Problem 507 10.26.2 Solution 507 10.26.3 Discussion 507 10.26.4 See Also 510 10.27 Using Patterns to Match Email Addresses and URLs 511 10.27.1 Problem 511 10.27.2 Solution 511 10.27.3 Discussion 511 10.28 Validation Using Table Metadata 512 10.28.1 Problem 512 10.28.2 Solution 512 10.28.3 Discussion 512 10.29 Validation Using a Lookup Table 515 10.29.1 Problem 515 10.29.2 Solution 515 10.29.3 Discussion 516 10.29.4 Issue Individual Queries 516 10.29.5 Construct a Hash from the Entire Lookup Table 516 10.29.6 Use a Hash as a Cache of Already-Seen Lookup Values 517 10.30 Converting Two-Digit Year Values to Four-Digit Form 518 10.30.1 Problem 518 10.30.2 Solution 518 10.30.3 Discussion 518 10.31 Performing Validity Checking on Date or Time Subparts 519 10.31.1 Problem 519 10.31.2 Solution 519 10.31.3 Discussion 519 10.32 Writing Date-Processing Utilities 522 10.32.1 Problem 522 10.32.2 Solution 522 10.32.3 Discussion 522 10.33 Using Dates with Missing Components 527 10.33.1 Problem 527 10.33.2 Solution 527 10.33.3 Discussion 527 10.34 Performing Date Conversion Using SQL 528 10.34.1 Problem 528 10.34.2 Solution 528 10.34.3 Discussion 528 10.34.4 See Also 530 10.35 Using Temporary Tables for Data Transformation 530 10.35.1 Problem 530 10.35.2 Solution 530 10.35.3 Discussion 530 10.36 Dealing with NULL Values 532 10.36.1 Problem 532 10.36.2 Solution 533 10.36.3 Discussion 533 10.37 Guessing Table Structure from a Datafile 536 10.37.1 Problem 536 10.37.2 Solution 536 10.37.3 Discussion 536 10.38 A LOAD DATA Diagnostic Utility 540 10.38.1 Problem 540 10.38.2 Solution 540 10.38.3 Discussion 540 10.39 Exchanging Data Between MySQL and Microsoft Access 546 10.39.1 Problem 546 10.39.2 Solution 546 10.39.3 Discussion 546 10.40 Exchanging Data Between MySQL and Microsoft Excel 547 10.40.1 Problem 547 10.40.2 Solution 547 10.40.3 Discussion 547 10.41 Exchanging Data Between MySQL and FileMaker Pro 549 10.41.1 Problem 549 10.41.2 Solution 549 10.41.3 Discussion 549 10.42 Exporting Query Results as XML 551 10.42.1 Problem 551 10.42.2 Solution 551 10.42.3 Discussion 551 10.43 Importing XML into MySQL 554 10.43.1 Problem 554 10.43.2 Solution 554 10.43.3 Discussion 554 10.44 Epilog 556 Chapter 11. Generating and Using Sequences 558 11.1 Introduction 559 11.2 Using AUTO_INCREMENT To Set Up a Sequence Column 560 11.2.1 Problem 560 11.2.2 Solution 560 11.2.3 Discussion 561 11.3 Generating Sequence Values 562 11.3.1 Problem 562 11.3.2 Solution 562 11.3.3 Discussion 562 11.4 Choosing the Type for a Sequence Column 564 11.4.1 Problem 564 11.4.2 Solution 564 11.4.3 Discussion 564 11.5 The Effect of Record Deletions on Sequence Generation 566 11.5.1 Problem 566 11.5.2 Solution 566 11.5.3 Discussion 567 11.6 Retrieving Sequence Values 569 11.6.1 Problem 569 11.6.2 Solution 569 11.6.3 Discussion 570 11.6.4 Using LAST_INSERT_ID( ) to Obtain AUTO_INCREMENT Values 570 11.6.5 Using API-Specific Methods to Obtain AUTO_INCREMENT Values 571 11.6.5.1 Perl 572 11.6.5.2 PHP 572 11.6.5.3 Python 572 11.6.5.4 Java 573 11.6.6 Server-Side and Client-Side SequenceValue Retrieval Compared 573 11.7 Determining Whether to Resequence a Column 574 11.7.1 Problem 574 11.7.2 Solution 574 11.7.3 Discussion 574 11.7.4 Reasons to Avoid Resequencing 575 11.8 Extending the Range of a Sequence Column 576 11.8.1 Problem 576 11.8.2 Solution 576 11.8.3 Discussion 576 11.9 Renumbering an Existing Sequence 577 11.9.1 Problem 577 11.9.2 Solution 577 11.9.3 Discussion 577 11.10 Reusing Values at the Top of a Sequence 578 11.10.1 Problem 578 11.10.2 Solution 578 11.10.3 Discussion 578 11.11 Ensuring That Rows Are Renumbered in a Particular Order 579 11.11.1 Problem 579 11.11.2 Solution 579 11.11.3 Discussion 579 11.12 Starting a Sequence at a Particular Value 580 11.12.1 Problem 580 11.12.2 Solution 580 11.12.3 Discussion 580 11.13 Sequencing an Unsequenced Table 582 11.13.1 Problem 582 11.13.2 Solution 582 11.13.3 Discussion 582 11.14 Using an AUTO_INCREMENT Column to Create Multiple Sequences 583 11.14.1 Problem 583 11.14.2 Solution 584 11.14.3 Discussion 584 11.15 Managing Multiple Si