-
![[Buch Cover]](../covers/mysqlspp.s.gif)
- Weitere Informationen zu diesem Buch:
Inhaltsverzeichnis | Index | Probekapitel | Kolophon | Rezensionen |
- Weitere Informationen zu diesem Buch:
First Edition April 2006
ISBN 978-0-596-10089-6
Weitere Informationen zu diesem Buch
Inhaltsverzeichnis |
Index |
Probekapitel |
Kolophon |
Rezensionen |
Index
[ Symbols ], [ A ], [ B ], [ C ], [ D ], [ E ], [ F ], [ G ], [ H ], [ I ], [ J ], [ K ], [ L ], [ M ], [ N ], [ O ], [ P ], [ Q ], [ R ], [ S ], [ T ], [ U ], [ V ], [ W ], [ X ], [ Y ], [ Z ],
Symbols[ Top ]
/* */ (multi-line comments), 60
( ) (parentheses), applying to expressions, 542
- - (single-line comments), 60
+ (addition) operator, 61
& (AND) operator, 62, 64
/ (division) operator, 61
= (is equal to) operator, 62
> (is greater than) operator, 62
< (is less than) operator, 62
<- (is less than or equal to) operator, 62
<>,!= (is not equal to) operator, 62
% (modulus) operator, 61
* (multiplication) operator, 61
<=> (null safe equal) operator, 62
| (OR) operator, 63, 64
; (semicolons) in code, 165
<< (shifts bits to left) operator, 64
>> (shifts bits to right) operator, 64
- (subtraction) operator, 61
~ (NOT or invert bits) operator, 64
A[ Top ]
ABS function, 218
abstraction, stored programs, 265
access, 140
column values, 369
DataReader metadata, 394
locks
deadlocks, 193
timeouts, 196
restricting, 430
result sets, DataSets, 395-397
SQLSTATE code, 140
tables
direct, 430
SQL tuning, 463-480
ACID (atomic, consistent, isolated, durable), 179
ACOS function, 223
actions, triggering, 250
ADDDATE function, 232
adding dummy ELSE clauses, 91
addition (+) operator, 61
ADDTIME function, 223
ADO.NET
applying, 401-412
calling, 391
dynamic result sets, 405-408
error handling, 154, 397
input parameters, 402
multiple result sets, 404
output parameters, retrieving, 410-412
overview of, 386-401
stored functions, calling, 412
stored programs
calling, 401
DataReader, 403
DataSets, 408-410
input parameters, 402
transaction management, 398-401
(see also .NET)
AFTER clause triggers, 251
algorithms
Fibonacci, 526
MERGE, 498
TEMPFILE, 498
aligning code, 542
ALTER PROCEDURE/FUNCTION statement, 173
ALTER ROUTINE privilege, 422
ANALYZE TABLE statements, 449
AND (&) operator, 62, 64
anti-joins, 493
(see also subqueries)
applications
J2EE, 323-332
PEAR, 150, 276
stored programs, 261-264
advantages of, 264-268
calling, 270-273
disadvantages of, 268-269
applying
ADO.NET, 401-412
ASCII string function, 206
ASP.NET, 413-418
cursors, 34, 101-112
DataReader, 393
error handling, 145-150
mysqli extension, 276-289
nondefault delimiters, 166
parameters
ADO.NET, 391
C#, 392
VB.NET, 391
prepared statements, PDOs, 293
Python, configuring interfaces, 380-385
SELECT-INTO statements, 101
stored functions, 244-248
stored programs
best practices, 539-544
blocks, 77-82
built-in functions, 64-68
comments, 60
EJBs, 329-331
MySQLdb, 373-379
servlets, 324-328
triggers, 251-256
views, 498
appropriate use of stored programs, xv
architecture exceptions, 534
Ascher, David, 372
ASCII functions, 206
ASIN function, 223
ASP.NET
applying, 413-418
forms, 414
assigning values to variables, 52
ATAN function, 223
atomic, consistent, isolated, durable (ACID), 179
attributes
connections, 346
IN, 53
INOUT, 53
OUT, 53
Perl, 346
audit logging, implementing, 253
authorization, stored procedures, 430
autocommit( ) method, 280, 370
AutoCommit attribute, 346, 353
avoiding, 563
accidental table scans, 472-475
deadlock conditions, 195
invalid data assignments, 550
nesting, 574
redundant code, 558
redundant evaluations, 88
rollbacks, 202
savepoints, 202, 563
self-joins, 515
side-effects (in programs), 573
sorts, creating indexes to, 501
table scans, 578
Axmark, David, 5
B[ Top ]
Balling, Derek, 14
batching inserts, 503
BDB (Berkeley-DB) database, 6
Bean Managed Persistence (BMP), 342
Bean-Managed Persistence (BMP), 330
Beaulieu, Alan, 14
BEFORE clause triggers, 251
BEGIN statement, 77
BEGIN-END blocks, 93, 137
BENCHMARK function, 233
Berkeley DB transaction support, 17, 180
Berkeley-DB (BDB) database, 6
best practices
coding style and conventions, 539-544
conditional logic, 551-554
development process, 533-539
dynamic SQL, 566-569
exception handling, 559-562
loop processing, 555-559
performance, 577-581
program construction, 569-577
SQL, 562-566
variables, 544-551
BETWEEN operator, 62
BIN function, 218
BINARY function, 216
bind variables, Perl, 348
binding
input parameters to stored programs, 299
parameters to prepared statements, 294
variable values, 566
bindParam( ) method, 294
bind_param( ) method, 280, 348
BIT_LENGTH function, 216
bitwise operators, 63
BLOB data types, 71
blocks, 77
BEGIN-END, 93
condition handler scope, 137
else, 370
except, 370
IF statements, 87
inner, overloading variables, 80
labels, 541
exiting, 81
naming, 79
nested, 79
stored programs, 77-82
try, 370
variables, overriding, 546
BMP (Bean Managed Persistence), 342
BMP (Bean-Managed Persistence), 330
books (as resources for developers), 13
browsers
Query Browser, 20-25
(see also interfaces)
buffer pools, 446
built-in functions (see functions, built-in)
C[ Top ]
C#, 386
DataSets, 409
populating, 395
error handling, 154
exception handling, 398
output parameters, ADO.NET, 410
parameters, applying, 392
stored functions, 412
stored procedures
calling input parameters, 402
DataReader, 403
transaction management, 398
cached result sets, 447
caching
queries, 446
SQL, 446
tables, 446
CALL statement, 38-39
CallableStatement interface, 317
calling
stored functions, 242-244, 273
ADO.NET, 412
stored programs, 38-39
ADO.NET, 386, 401
from application code, 270-273
error handlers, 127
Java, 309
mysqli extension, 284
PDO, 297
Perl, 343
PHP, 45, 275
Python, 364
cardinality, 449
CASE statements, 8, 88-92
formatting, 580
IF statements, comparing, 92
inclusive, 553
optimizing, 522-526
stored functions, 245
catching
errors, PDO, 291
SQLExceptions, 316
CEILING function, 218
CHAR function, 207
CHARACTER_LENGTH function, 216
CHAR_LENGTH function, 216
CHARSET function, 208
check constraint triggers, 254
checking types (in Java), 73
Christensen, Clayton, xiii
classes, StoredProcedure, 341
Class.forName( ) method, 310
clauses
AFTER triggers, 251
BEFORE triggers, 251
DETERMINISTIC, 43, 170, 171, 240
ELSE, 89
adding dummy, 91
END IF, 84
FROM, subqueries, 495-500
GROUP BY, 501-502
INTO, SELECT statements, 100
ON FUNCTION, 423
ON PROCEDURE, 423
ORDER, 501-502
SQL, 240
THEN, 84
clients
applications, 261
traffic, reducing, 509
CLOSE statements, cursors, 102
closing stored programs, 273
CMP (Container-Managed Persistence), 330, 342
COALESCE function, 234
code
aligning, 542
conditional control statements, 82-93
conventions, 539-544
injection (stored programs), 434-440
readability, 561
redundant, avoiding, 558
reviewing, 534
segments, grouping, 78
semicolons in, 165
SQLCODE, accessing, 140
SQLSTATE, 133
accessing, 140
error handling, 134
named conditions, 139
stored programs
CASE statements, 522-526
cursors, 528
IF statements, 522-526
loops, 519-522
optimizing, 508
recursion, 526-527
reducing traffic, 512-515
speed, 509-512
SQL statements, 515-519
trigger overhead, 529-531
styles, 539-544
testing, 534
walkthroughs, 536
columns
Extra, in Explain statement, 453
id, in EXPLAIN statement, 451
key, 453
key, in EXPAIN statement, 453
key_len, in EXPLAIN statement, 453
maintaining, 45
possible_keys, 453
possible_keys, in EXPLAIN statement, 453
ref, in EXPLAIN statement, 453
rows in EXPLAIN statement, 453
select_type, in EXPLAIN statement, 451
tables, in EXPLAIN statement, 452
types, in EXPLAIN statement, 452
updating, 120
values
accessing, 369
triggers, 250
(see also tables)
COMMENT comment_string, 171
comments, 60, 447, 543
adding, 544
multi-line (/* */), 60
single-line (- -), 60
commit( ) method, 280, 370
COMMIT statement, 182, 292, 353
common routines, stored programs, 267
comparing IF/CASE statements, 92
comparison operators, 61
complex joins, subqueries, 490
COMPRESS function, 216
computationally inferior, stored programs, 268
CONCAT function, 208-210
concatenation
indexes, 468
table scans, avoiding, 474
CONCAT_WS function, 210
concurrency, 181
condition handler scope, 137
conditional control statements, 82-93
conditional execution of stored procedures, 30
conditional logic, 8
best practices, 551-554
statements, 8
conditional structures, optimizing, 580
conditions
deadlock, avoiding, 195
ELSEIF, 87
error handling, 129-138
named, 139
errors
cursors, 111
last row, 128-129
NOT FOUND
nested cursor loops, 108
resetting, 564
overlapping, 86
redundancy, 88
testing, 522, 552
configuring
Hibernate, 337
IDEs, 310
ADO.NET, 386
output parameters, 272
stored programs for execution, 270
connections
attributes, 346
MySQL
ADO.NET, 387-390
JDBC, 310
mysqli extensions, 277
PDO, 290
Perl, 346
VB.NET, 389
MySQLdb extensions, 365
Connector/Net drivers
installing, 386
registering, 387-390
Container-Managed Persistence (CMP), 330, 342
CONTINUE condition handlers, 130, 132
CONTINUE statements, avoiding processing loops, 520
controls
iterative, 8
stored programs, 8
CONV function, 219
conventions, code, 539-544
CONVERT_TZ function, 223
correlated updates, optimizing, 517
COT function, 223
covering indexes, 470
CRC32 function, 223
CREATE FUNCTION statement, 21, 171, 238
CREATE PROCEDURE statement, 21, 160, 170-171
syntax, 170
CREATE ROUTINE privilege, 422
CREATE TRIGGER statement, 21, 172, 249
createDemoTables( ) method, 316
createQuery( ) method, 335
createStatement( ) method, 312
CURDATE function, 232
CURRENT_DATE function, 224
CURRENT_TIME function, 224
CURRENT_TIMESTAMP function, 224
CURRENT_USER function, 234
cursors
applying, 34
creating, 101-112
defining, 101
DictCursor, 369
error conditions, 111
loops, 103
exiting, 111
nesting, 108-111
NOT FOUND handlers, 128
types of, 105-108
optimizing, 528
REPEAT loops, 105
statements, 102
trigger overhead, 529-531
CURTIME function, 232
D[ Top ]
dangling transactions, 562
Data Definition Language (see DDL)
Data Manipulation Language (see DML)
data types, 68-71
DATABASE function, 234
databases
BDB, 6
information, retrieving, 173-175
interacting with, 33
Java, 261
.NET, 261
Perl, 261, 346
PHP, 261
Python, 261
security, stored programs, 264
stored programs, 3-7
transactions
defining, 183
design, 201-203
isolation levels, 181-182
locks, 190-201
savepoints, 185-190
statements, 182
support, 180
(see also MySQL)
DataReader
applying, 393
DataSets, 395-397
dynamic result sets, 406
metadata, retrieving, 394
multiple result sets, 404
stored programs, 403
DataSets, 395-397
stored programs, 408-410
date and time functions (see functions, built-in)
date data types, 71
DATE function, 224
date literals, 52
DATE_ADD function, 224
DATEDIFF function, 227
DATE_FORMAT function, 225
DATE_SUB function, 226
DAY function, 225, 228
DAY_HOUR function, 225
DAY_MINUTE function, 225
DAYNAME function, 228
DAYOFMONTH function, 232
DAYOFWEEK function, 228
DAYOFYEAR function, 228
DAY_SECOND function, 225
DBD::mysql driver, 344-354
executing, 354-361
DBI->connect( ) method, 346
DDL (Data Definition Language), 185
deadlocks, 193
(see also locks)
DEALLOCATE statement, 119
DECLARE CONTINUE HANDLER statement, 127
DECLARE HANDLER statement, 129
DECLARE statement, 7, 25, 26, 50
cursors, defining, 101
declaring
blocks, 78
condition handlers, 135
multiple variables, 50
variables, 25, 27, 51
overriding, 546
DECODE function, 217
definer rights security, 421, 424, 568
policies, implementing, 427-431
defining
cursors, 101
loaders, 334
transactions, 183
DEGREES function, 223
deleting variables, 547
DELIMITER statements, 166
derived data, maintaining, 252
derived tables, 495
design, transactions, 201-203
DETERMINISTIC clause, 43, 170, 171, 240
Detron HB, 5
development process, best practices, 533-539
dialog boxes, Eclipse, 310
DictCursor, 369
direct access to tables, 430
directives (see hints)
Directives region, 388
dirty read (see READ UNCOMMITTED isolation level)
DIV (integer division) operator, 61
division (/) operator, 61
division of duties, stored programs, 268
DML (Data Manipulation Language), tuning, 503-506
do( ) method, 347
drivers
Connector/.NET
installing, 386
registering, 387-390
DBD::mysql, 344-354
executing, 354-361
JDBC
installing, 310
registering, 310
DROP PROCEDURE statement, 160
DROP statement, 173
DuBois, Paul, 13
dummy ELSE clauses, adding, 91
dump_results method, 351
Dyer, Russell, 13
dynamic result sets
ADO.NET, 405-408
Perl, 356
processing, 282, 296
Python, 376
dynamic SQL
best practices, 566-569
server-side prepared statements, 118-123
dynamic variable typing (in PHP), 74
E[ Top ]
Eckel, Bruce, 76
Eclipse, xiii
Eclipse dialog box, 310
editing stored programs, 159
EMACs, 160
existing programs, 166-168
System Editor, 160
Toad, 165
editors, creating stored programs, 161
EJBs (Enterprise JavaBeans), 323
stored programs, applying, 329-331
else blocks, 370
ELSE clauses, 89
dummy, adding, 91
ELSEIF conditions, 87
applying with mutually exclusive clauses, 551
ELT function, 217
Emacs, editing stored programs, 160
embedding
non-SELECT statements, 37, 100
stored programs, 7
UPDATE statements, 38
emulating SIGNAL statements, 143
enabling
mysqli extensions, 276
transactions, 183
encapsulating functions, 569
ENCODE function, 217
ENCRYPT function, 217
END IF clause, 84
END LOOP statement, 93
END statement, 77
Enterprise JavaBeans (EJBs), 323
stored programs, applying, 329-331
entity EJBs, 328
ENUM data types, 68
error conditions, cursors, 111
error handling, 10
ADO.NET, 154, 397
applying, 145-150
C#, 154
conditions, 129-138
DBD::mysql drivers, 347
deadlocks, 193
Java/JDBC, 152, 316
last row conditions, 128-129
mysqli extensions, 277
named conditions, 139
.NET, 154
overview of, 125-128
Perl, 151
PHP, 150
Python, 153
SQL, 123
stored programs, 10, 32
stored programs in calling applications, 150-155
VB.NET, 155, 397
error messages, 78
error status methods, PDO, 291
errorInfo( ) method, 291
events, deadlocks, 193
except blocks, 370
exceptions
architecture, 534
best practices, 559-562
C#, 398
MySQLdb extensions, 366
SIGNAL statements, creating with, 141
SQLExceptions, throwing, 316
exec( ) method, 290
execute( ) method, passing parameters, 367
EXECUTE statements
PREPARE statement, 118
privileges, 422
ExecuteNonQuery( ) method, 390
executeQuery( ) method, 312
ExecuteReader( ) method, 393
executeUpdate( ) method, 312
executing
dynamic SQL, 119
Hibernate queries, 336
non-SELECT statements
mysqli extensions, 278
PDO, 290
SQL statements, MySQLdb extensions, 367
stored procedures, JDBC, 320
stored programs, 30, 270, 272
DBD::mysql drivers, 354-361
security options, 423-434
existing stored programs, editing, 166-168
EXIT condition handlers, 130, 132
exiting
cursor loops, 111
labeled blocks, 81
EXP function, 223
EXPLAIN EXTENDED statement, 454
EXPLAIN statement, 449-459
exponential degradation, nonindexed subqueries, 488
EXPORT_SET function, 217
expressions, 64
functions, replacing, 546
NULL, 548
parentheses ( ), applying, 542
TRUE/FALSE, 83
extensions
MySQLdb, 364-379
applying, 380-385
mysqli, 276
applying, 276-289
PEAR, 150, 276
external files, maintaining stored programs in, 167
Extra column in EXPLAIN statement, 453
EXTRACT function, 228
F[ Top ]
FALSE, IF statements, 83
FETCH statements, cursors, 102
fetchall( ) method, 368
fetch_field( ) method, 288
fetching
multiple result sets, 288
single rows from cursors, 103
fetchmany( ) method, 369
fetch_object( ) method, 278, 284
fetchone( ) method, 368
fetch_row( ) method, 279
fetchrow_array method, 349
fetchrow_arrayref method, 349
fetchrow_hashref method, 349
Feuerstein, Steven, 13
Fibonacci sequences, 526
FIELD function, 217
FieldCount( ) method, 394
files, maintaining in external, 167
finder method (EJB), 331
FLOOR function, 219
flow of control, statements, 82-93
flow of execution, stored statements, 30
FORCE INDEX hint, 467, 472
<form> tag, 383
FORMAT numeric function, 219
formatting
CASE statements, 580
connections, MySQLdb extensions, 365
cursors, 101-112
exceptions, SIGNAL statements, 141
IF statements, 85, 580
indexes, 467, 578
avoiding sorts, 501
stored functions, 238-242
stored programs, 159-166
temporary tables, 117
triggers, 249-251
forms, ASP.NET, 414
FOUND error handler, 33
fragmentation, logic, 269
FROM clauses, subqueries, 495-500
FROM_DAYS function, 233
full table scans, 463, 464
functions
built-in, 64-68, 205-237
ABS, 218
ACOS, 223
ADDDATE, 232
ADDTIME, 223
ASCII, 206
ASIN, 223
ATAN, 223
BENCHMARK, 233
BIN, 218
BINARY, 216
BIT_LENGTH, 216
CEILING, 218
CHAR, 207
CHARACTER_LENGTH, 216
CHAR_LENGTH, 216
CHARSET, 208
COALESCE, 234
COMPRESS, 216
CONCAT, 208-210
CONCAT_WS, 210
CONV, 219
CONVERT_TZ, 223
COT, 223
CRC32, 223
CURDATE, 232
CURRENT_DATE, 224
CURRENT_TIME, 224
CURRENT_TIMESTAMP, 224
CURRENT_USER, 234
CURTIME, 232
DATABASE, 234
DATE, 224
DATE_ADD, 224
DATEDIFF, 227
DATE_FORMAT, 225
DATE_SUB, 226
DAY, 225, 228
DAY_HOUR, 225
DAY_MINUTE, 225
DAYNAME, 228
DAYOFMONTH, 232
DAYOFWEEK, 228
DAYOFYEAR, 228
DAY_SECOND, 225
DECODE, 217
DEGREES, 223
ELT, 217
ENCODE, 217
ENCRYPT, 217
EXP, 223
EXPORT_SET, 217
EXTRACT, 228
FIELD, 217
FLOOR, 219
FORMAT, 219
FROM_DAYS, 233
GETFORMAT, 229
GET_LOCK, 234
HEX, 219
HOUR, 225, 233
HOUR_MINUTE, 225
HOUR_SECOND, 225
IFNULL, 235
INET_ATON, 217
INET_NTOA, 217
INSERT, 211
INSTR, 212
INTERVAL, 235
IS_FREE_LOCK, 235
ISNULL, 235
LAST_DAY, 233
LCASE, 212
LEAST, 219
LEFT, 212
LENGTH, 212
LN, 223
LOAD_FILE, 213
LOCALTIME, 233
LOCALTIMESTAMP, 233
LOCATE, 213
LOG, 223
LOG2, 223
LOG10, 223
LOWER, 217
LPAD, 214
LTRIM, 214
MAKEDATE, 229
MAKETIME, 229
MICROSECOND, 233
MID, 217
MINUTE, 225, 233
MINUTE_SECOND, 225
MOD, 220
MONTH, 225, 233
MONTHNAME, 230
NOW, 230
NULLIF, 235
numeric, 218-223
OCTET_LENGTH, 217
ORD, 217
PASSWORD, 217
PERIOD_ADD, 233
PERIOD_DIFF, 233
PI, 223
POSITION, 217
POWER, 221
QUARTER, 233
QUOTE, 217
RADIANS, 223
RAND, 221
RELEASE_LOCK, 236
REPEAT, 215
REPLACE, 215
REVERSE, 217
RIGHT, 217
ROUND, 222
RPAD, 215
RTRIM, 215
SECOND, 225, 233
SEC_TO_TIME, 230
SESSION_USER, 236
SHA, 217
SHA1, 217
SIGN, 222
SIN, 223
SORT, 222
SOUNDEX, 217
SPACE, 217
STRCMP, 215
string, 205-218
STR_TO_DATE, 230
SUBDATE, 233
SUBSTRING, 215
SUBSTRING_INDEX, 217
SUBTIME, 233
SYSDATE, 233
TAN, 223
TIMEDIFF, 231
TIMESTAMP, 231
TIMESTAMPADD, 231
TIMESTAMPDIFF, 231
TIME_TO_SEC, 230
TO_DAYS, 233
TRIM, 216
UCASE, 216
UNCOMPRESS, 218
UNCOMPRESSED_LENGTH, 217
UNHEX, 218
UPPER, 218
USER, 236
UUID, 236
functions
VERSION, 236
WEEK, 231
WEEKDAY, 232
WEEKOFYEAR, 233
YEAR, 225, 232
YEARWEEK, 232
CREATE FUNCTION statement, 21, 171, 238
encapsulating, 569
expressions, replacing, 546
html_table, 384
IF statements, 67
indexes, suppressing, 473
limiting, 575
mathematical, 65
SET statements, 67
stored, 4, 6, 9
ADO.NET, 412
applying, 244-248
best practices, 565
calling, 242-244, 273
creating, 238-242
SQL statements in, 242
UDFs, 244
G[ Top ]
generating statistics, 513
getColumnMeta( ) method, 295
getErrorCode( ) method, 152, 317
GetFieldType( ) method, 394
GETFORMAT function, 229
GetInt32( ) method, 393
GET_LOCK function, 234
getMessage( ) method, 152, 317
GetName( ) method, 394
getNamedQuery( ) method, 335
getSQLState( ) method, 152
getStackTrace( ) method, 317
GetString( ) method, 393
global variables, 549
user variables, applying as, 59
GRANT statement, 423
GROUP BY clause, 501-502
grouping code segments, 78
H[ Top ]
handling, 398
dynamic result sets, Perl, 356
exceptions
best practices, 559-562
MySQLdb extensions, 366
invoker rights security, 433
multiple result sets, 299
Perl, 355
output parameters, 287, 302
output variables, Perl, 357
transactions, JDBC, 315
(see also error handling)
Harrison, Guy, 13
HEX function, 219
Hibernate, stored procedures, 332-337
hints
FORCE INDEX, 467, 472
IGNORE INDEX, 466
manually choosing an index, 466
optimizer, 455, 465
USE INDEX, 466
history of MySQL, 5-7
hostnames, Perl, 346
HOUR function, 225, 233
HOUR_MINUTE function, 225
HOUR_SECOND function, 225
HTML (Hypertext Markup Language), 381
html_table function, 384
Hypertext Markup Language (see HTML)
I[ Top ]
id column in EXPLAIN statement, 451
IDE (Integrated Development Environment)
ADO.NET, configuring, 386
configuring, 310
IF statements, 8, 82-88
CASE statements, comparing, 92
formatting, 580
functions, 67
optimizing, 522-526
tuning, 524
IFNULL function, 235
IF-THEN-ELSE statements, 85, 86
IGNORE INDEX hint, 466
implementing
logging, 253
security policies, 427-431
IN attribute, 53
IN operator, 62
IN parameter, 28
inclusive CASE statements, 553
increasing sort memory, 502
Indexed Sequential Access Method (ISAM), 6
indexes
concatenating, 468
avoiding table scans, 474
covering, 470
creating, 578
FORCE INDEX hint, 467, 472
IGNORE INDEX hint, 466
joins, with/without, 481
lookups, 464
manually choosing, 466
multiple, merging, 469
overhead, 466, 503
prefixed, 467
searching, 473
selecting, 466-469, 471
selective, 449
sorts, avoiding, 501
statistics, viewing, 448
subqueries, 488
suppressing
functions, 473
substrings, 473
triggers, 531
USE INDEX hint, 466
INET_ATON function, 217
INET_NTOA function, 217
infinite loops, 94
INFORMATION_SCHEMA.ROUTINES table, 174
INFORMATION_SCHEMA.TRIGGERS table, 175
injection
code, stored programs, 434-440
SQL, 123
inline views, 495
inner blocks, overloading variables, 80
InnoDB
statistics, 456
transaction support, 180
INNODB_BUFFER_POOL_SIZE parameter, 446
INOUT attribute, 53
INOUT parameter, 28
input parameters
ADO.NET, 402
binding to stored programs, 299
supplying, 319
INSERT function, 211
INSERT statements, triggers, 44
inserting AUDIT_LOG tables, 187
inserts, batching, 503
installing
Connector/Net drivers, 386
DBD::mysql drivers, 344
JDBC, 310
MySQLdb extensions, 364
instances, connecting, 310
INSTR function, 212
integers
data types, limitations of, 71
division (DIV) operator, 61
Integrated Development Environment (see IDE)
integration, SQL, 7
interacting with databases, 33
interfaces
CallableStatement, 317
PHP, error handling, 151
Internet resources, 14
INTERVAL function, 235
INTO clauses, SELECT statements, 100
invalid data assignments, avoiding, 550
invariant expressions, moving loop, 579
invoker rights security, 422, 568
handling, 433
stored procedures, 426
is equal to (=) operator, 62
is greater than (>) operator, 62
is less than (<) operator, 62
is less than or equal to (<-) operator, 62
is not equal to (<>, !=) operator, 62
IS NULL operator, 62
ISAM (Indexed Sequential Access Method), 6
IS_FREE_LOCK function, 235
ISNULL function, 235
isolation levels, 181-182
issuing
non-SELECT statements
ADO.NET, 390, 393
JDBC, 312
one-off queries, PDOs, 292
one-off statements, Perl, 347
queries, Perl, 348
SELECT statements
ADO.NET, 393
JDBC, 312
ITERATE statement, 95
iterative controls, 8
iterative processing, loops, 93-98, 521
J[ Top ]
J2EE applications, 323-332
Java, 261, 310-342
CallableStatement interface, 317
calling, 273
connections, 310
EJBs, 328-332
error handling, 152, 316
Hibernate, 332-337
queries, 335
support, 333
IDE, configuring, 310
input parameters, 319
non-SELECT statement, 312
objects, loading, 333
OUT variables, 319
output parameter values, 322
overhead, 512
persistence, 336
prepared statements, 313
procedures, executing, 320
result sets, 321
metadata, 313
retrieving, 312
servlets, 324-328
Spring, 337-342
statistics, generating, 513
transactions, handling, 315
type checking, 73
JBoss, xiii
JDBC (Java Database Connectivity)
error handling, 152
overview of, 310-317
stored functions, 244
stored programs, applying, 317-323
joins
anti-joins, tuning, 493-496
example of, 483
with indexes, 481
without indexes, 480
ordering, 482
overhead, 480
self-joins, avoiding, 515
single-sweep multi-joins, 480
SQL tuning, 480-484
subqueries
applying in complex, 490
rewriting as, 488
tables, 480
K[ Top ]
key caches, 446
key column in EXPLAIN statement, 453
KEY_BUFFER_SIZE parameter, 446
key_len column in EXPLAIN statement, 453
keywords
language SQL, 170
MySQLConnection object, 388
SQL SECURITY {DEFINER|INVOKER}, 171
Kline, Kevin, 14
Krukenberg, Michael, 14
L[ Top ]
labels, 541
blocks, 79
exiting, 81
LAMJ (Linux-Apache-MySQL-JBoss), xiii
LAMP (Linux-Apache-MySQL-PHP/Perl/Python), xiii, 275
LANGUAGE SQL keyword, 170
languages
built-in functions, 64-68
data types, 68-71
expressions, 64
Java, 261, 310-342
literals, 51-60
.NET, 261, 386-418
operators, 60
bitwise, 63
comparison, 61
logical, 62-63
mathematical, 61
Perl, 261, 343-363
PHP, 261, 275-308
PL/SQL, 7
Python, 261, 364-385
SQL, 7
stored programs
applying, 4
strict mode, 72-76
Transact SQL, 7
variables, 49-51
(see also specific languages)
Larsson, Allan, 5
last row conditions, error handling, 128-129
LAST_DAY function, 233
LCASE function, 212
LEAST function, 219
LEAVE statements, 94
block labels, 81
loops
avoiding processing, 520
in simple, 557
REPEAT loops, 107
LEFT function, 212
LENGTH function, 212
LIKE operator, 62
limitations
of functions, 575
of integer data types, 71
Linux, xiii
installing DBD::mysql driver, 344
literals, 50
date, 52
languages, 51-60
numeric, 51
string, 52
LN function, 223
load( ) method, 333
LOAD_FILE function, 213
loading objects, stored procedures, 333
local variables
declaring, 25, 27
SELECT INTO syntax, 33-37
LOCALTIME function, 233
LOCALTIMESTAMP function, 233
LOCATE function, 213
LOCK TABLES statement, 183
locks
best practices, 563
strategies, 197
transactions, 190-201
LOG function, 223
LOG10 function, 223
LOG2 function, 223
logging
implementing, 253
slow query logs, 458
logic, fragmentation, 269
logical operators, 62-63
lookups, indexes, 464
LOOP statements, 93
looping, 8
loop-invariant statements, 519
LOOP-LEAVE-END LOOP cursor loop, 105
loops
best practices, 555-559
cursors, 103
exiting, 111
nesting, 108-111
NOT FOUND handlers, 128
types of, 105-108
execution, restarting, 95
infinite, 94
invariant expressions, moving, 579
iterative processing, 93-98
labels, 541
nested, 97
operators, 60
optimizing, 519-522
overhead, 520
REPEAT, 95
cursors, 105
LEAVE statements, 107
REPEAT UNTIL, 8
stored procedures, 31
terminating, 94, 555
UNTIL, 95
WHILE, 8, 96
LOWER function, 217
LPAD function, 214
LTRIM function, 214
M[ Top ]
maintainability of stored programs, xv, xvi
maintaining
columns, 45
derived data, 252
stored programs in external files, 167
MAKEDATE function, 229
MAKETIME function, 229
management
stored programs, 170-173
transactions
ADO.NET, 398-401
C#, 398
defining, 183
design, 201-203
isolation levels, 181-182
locks, 190-201
mysqli extensions, 279
overhead, 180
PDO, 292
Perl, 353
Python, 370
savepoints, 185-190
statements, 182
support, 180
VB.NET, 400
manually choosing an index, 466
mapping query stored procedures, 335
Martelli, Ascher, 372
mathematical functions, 65
mathematical operators, 61
measuring
SHOW STATUS statement statistics, 458
SQL, 456
memory
overhead, reducing, 545
sorts, increasing, 502
SQL, caching, 446
MERGE algorithm, 498
merging
multiple indexes, 469
tables, 478-480
messages, error, 78
metadata
DataReader, retrieving, 394
Python, retrieving, 371
result sets, retrieving, 282, 295, 313, 352
methods
autocommit( ), 280, 370
bindParam( ), 294
bind_param( ), 280, 348
Class.forName( ), 310
commit( ), 280, 370
createDemoTables( ), 316
createQuery( ), 335
createStatement( ), 312
DBI->connect( ), 346
do( ), 347
dump_results, 351
errorInfo( ), 291
exec( ), 290
execute( ), passing parameters, 367
ExecuteNonQuery( ), 390
executeQuery( ), 312
ExecuteReader( ), 393
executeUpdate( ), 312
fetch_array, 349
fetch_field( ), 288
fetchmany( ), 369
fetch_object( ), 278, 284
fetchone( ), 368
fetch_row( ), 279
fetchrow_arrayref, 349
fetchrow_hashref, 349
fetechall( ), 368
FieldCount( ), 394
finder (EJB), 331
getColumnMeta( ), 295
getErrorCode( ), 152, 317
GetFieldType( ), 394
GetInt32( ), 393
getMessage( ), 152, 317
GetName( ), 394
getNamedQuery( ), 335
getSQLState( ), 152
getStackTrace( ), 317
GetString( ), 393
load( ), 333
multi_query( ), 288
MySqlDataAdaptor( ), 397
next( ), 312
nextset( ), 376
open( ), 389
prepare( ), 280
prepareCall( ), 317
printStackTrace( ), 317
query( ), 285
PDO, 292
Read( ), 393
result( ), 288
rollback( ), 280, 370
setAutocommit( ), 315
setUpdate( ), 315
store_result( ), 288
MICROSECOND function, 233
Microsoft SQL Server-based applications, 262
MID function, 217
MINUTE function, 225, 233
MINUTE_SECOND function, 225
MOD numeric function, 220
modes
parameters, 28
strict, 72-76
Mod_python input forms, 382
modularization, 572
modules, standardizing, 571
modulus (%) operator, 61
MONTH function, 225, 233
MONTHNAME function, 230
moving loop invariant expressions, 579
multi-line comments (*/ */), 60
multiple indexes, merging, 469
multiple result sets
ADO.NET, 404
handling, 299
Perl, 355
retrieving, 287
JDBC, 320
multiple triggers, 576
multiple variables, declaring, 50
multiplication (*) operator, 61
multi_query( ) method, 288
multistatement IF statements, 84
MyISAM transaction support, 180
MySQL
connecting
ADO.NET, 387-390
JDBC, 310
mysqli extensions, 277
Perl, 346
VBNET, 389
history of, 5-7
Query Browser, creating stored programs, 161
MySQL START TRANSACTION statement, 353
MySQLConnection object, 388
MySqlDataAdaptor( ) method, 397
MySqlDataAdaptor object, 395
MySqlDataReader, 393
MySQLdb extension, 364-379
applying, 380-385
stored programs, 373-379
mysqli extension, 276
applying, 276-289
MyTrace.pl utility, 461
N[ Top ]
named conditions, error handling, 139
naming
blocks, 79
parameters, 53
variables, 52, 545
nested blocks, 79
nested CASE statements, 91
nested cursor loops, 108-111
nested IF statements, 84
redundant evaluations, avoiding, 88
nested loops, 97
nested transactions, applying savepoints, 189
nesting, avoiding, 574
.NET, 261, 386-418
ADO.NET (see ADO.NET)
calling, 273, 391
error handling, 154
networks, reducing traffic, 265
next( ) method, 312
nextset( ) method, 376
NO SQL|CONTAINS SQL|READS SQL DATA|MODIFIES SQL DATA, 171
nondefault delimiters, applying, 166
non-SELECT statements
ADO.NET, 390, 393
embedding, 37
JDBC, 312
mysqli extensions, 278
PDO, 290
stored programs, applying, 99-100
non-strict type checking, 74
NOT BETWEEN operator, 62
NOT DETERMINISTIC, 171
NOT FOUND condition
nested cursor loops, 108
resetting, 564
NOT FOUND handlers, 33, 91, 105
cursor loops, 128
NOT IN operator, 62
NOW function, 230
null safe equal (<=>), 62
NULL values, 55, 62
aggregate functions, 65
comparison operators, 61
condition handlers, 137
dynamic SQL, 121
ENUM data type, 69
exceptions, 138
failure points (for stored procedures), 146
functions, 66
logical operators, 62
multiple variables, 50
OUT variables, 54
variables, 50
NULL values, variables, 50
NULLIF function, 235
numbers, prime
implementing, 511
searching, 512
numeric data types, 71
numeric functions (see functions, built-in)
numeric literals, 51
O[ Top ]
Object-Relational Mapping (ORM) framework, 332
objects
DataReader
dynamic result sets, 406
multiple result sets, 404
stored programs, 403
DataSets, 395-397
MySQLConnection, 388
MySqldataAdaptor, 395
PDO (PHP Data Objects), 150, 276, 289-307
POJO (Plain Old Java Objects), 337
scope, controlling, 78
statements, reusing, 391
stored procedures, loading, 333
obtaining output parameters, 379
OCTET_LENGTH function, 217
ON FUNCTION clause, 423
ON PROCEDURE clause, 423
one-off queries, issuing, 292
one-off statements, issuing, 347
open( ) method, 389
OPEN statements, cursors, 102
operators, 60
bitwise, 63
comparison, 61
logical, 62-63
mathematical, 61
optimistic locking strategy, 198, 199, 202, 563
optimizer hints, 455, 465
optimizing
conditional structures, 580
correlated updates, 517
readability, 561
SQL tuning, 443
anti-joins, 493-496
DML, 503-506
examples of, 459-461
FROM clause, 495-500
GROUP BY clause, 501-502
joins, 480-484
ORDER clause, 501-502
statements, 449-459
subqueries, 486-493
table access, 463-480
stored programs, 508
CASE statements, 522-526
cursors, 528
IF statements, 522-526
loops, 519-522
recursion, 526-527
reducing traffic, 512-515
speed, 509-512
SQL statements, 515-519
trigger overhead, 529-531
table scans, 475-478, 578
options, PHP, 276
OR (|) operator, 63, 64
Oracle
applications, 262
prime number implementation, 511
ORD function, 217
ORDER clause, 501-502
ordering joins, 482
orders of magnitude, 443
ORM (Object-Relational Mapping) framework, 332
OUT attribute, 53
OUT parameter, 28
OUT variables, registering, 319
output
EXPLAIN statements, 450, 451
parameters
ADO.NET, 410-412
configuring, 272
handling, 287, 302
obtaining, 379
retrieving, 272
retrieving values, 322
variables, Perl, 357
overhead
AUDIT_LOG table, inserting/rolling back, 187
client/server traffic, 509
disk I/O, retrieving data from caches, 446
disk-based sorts, 501
eliminating, 509
Hibernate, 309
indexes, 466, 503
INSERT, 530
Java, 512
joins, 480
locking mechanisms, 203
loops, 520
memory, reducing, 545
merge tables, 479
network, reducing, 267
performance, 88
measuring, 506
prepared statements, 314
reducing, 119
processing, adding, 524
SQL processing, 524
statements, 91
re-executing, 280
stored programs, advantages of, 269
tables, scanning, 466, 478
transaction logs, 505
transaction management, 180
triggers, 256, 529-531
overlapping
condition handlers, 136
conditions, 86
overloading variables, 80
overriding
blocks, 80
variables, 546
P[ Top ]
pair programming, 536
parameters, 27-29, 53
ADO.NET, applying, 391
binding
prepared statements, 294
resisting code injection, 437
C#, applying, 392
input
ADO.NET, 402
binding to stored programs, 299
supplying, 319
output
ADO.NET, 410-412
configuring, 272
handling, 287, 302
obtaining, 379
retrieving, 272
retrieving values, 322
passing, MySQLdb extensions, 367
registering, 270
stored functions, 240
values, validating, 567
VB.NET, applying, 391
parentheses ( ), applying to expressions, 542
parsing SQL, 445
partial indexes, 467
partitioning tables, 478-480
passing parameters, MySQLdb extensions, 367
PASSWORD function, 217
pasting SQL parameters, 391
PDO (PHP Data Objects), 45, 150, 276, 289-307
PEAR (PHP Extension and Application Repository), 150, 276
performance
best practices, 577-581
overhead, 88
stored programs, xv
CASE statements, 522-526
cursors, 528
IF statements, 522-526
loops, 519-522
optimizing, 508
recursion, 526-527
reducing traffic, 512-515
speed, 509-512
SQL statements, 515-519
trigger overhead, 529-531
PERIOD_ADD function, 233
PERIOD_DIFF function, 233
Perl, xiii, 261, 343-363
DBD::mysql drivers
bind variables, 348
connecting, 346
dynamic result sets, 356
error handling, 347
example of, 358-361
executing stored programs, 354
installing, 344
issuing simple one-off statements, 347
multiple result sets, 355
output variables, 357
queries, 348
result set metadata, 352
retrieving rows, 349-352
reusing statements, 348
transaction management, 353
error handling, 151
stored programs
DBD::mysql drivers, 344-354
executing with DBD::mysql drivers, 354-361
permissions
stored programs, 422
(see also security, privileges)
persistence, 330, 336, 342
stored procedures, 336
pessimistic locking strategy, 198, 202, 563
PHP, xiii, 261, 275-308
catching errors, 290
checking for errors, 277
connections, 277, 290
dynamic variable typing, 74
error handling, 150
example of, 303-308
MySQL extension (ext/mysql), 276
non-SELECT statement, 278, 290
one-off queries, issuing, 292
output parameters, 287, 302
parameters, binding, 294
prepared statements, 280, 293
result sets
dynamic, 282, 296
metadata, 282, 295
multiple, 287, 299
prepared statements, 281
retrieving, 278
result sets, retrieving from, 114-117
stored procedures, calling, 45
stored programs
binding input parameters, 299
calling, 244, 284
calling with PDO, 297
mysqli extension, 276-289
options, 276
PDO, 289-307
transaction management, 279, 292
PHP Data Objects (PDO), 45, 150, 276, 289-307
PHP Extension and Application Repository (PEAR), 150, 276
PI function, 223
Pipes, Jay, 14
PL/SQL language (Oracle), 7, 16, 49, 163, 262
POJO (Plain Old Java Objects), 337
policies, security, 427-431
populating
databases, triggers, 45
DataSets
C#, 395
VB.NET, 396
portability, stored programs, 268, 269
ports, Perl, 346
POSITION function, 217
possible_keys column in EXPLAIN statement, 453
POWER function, 221
precedence, condition handlers, 136
prefixed indexes, 467
prepare( ) method, 280
PREPARE statements, 118
prepareCall( ) method, 317
prepared statements
bind variables, 348
JDBC, 313
mysqli extensions, 280
overhead, reducing, 119
parameters, binding, 294
PDO, 293
result sets, retrieving, 281
prime numbers
implementing, 511
searching, 512
PrintError attribute, 346
printStackTrace( ) method, 317
privileges
ALTER ROUTINE, 422
creating stored programs, 422
EXECUTE, 422
executing stored programs, 423
procedural logic, avoiding self-joins with, 515
procedures, 6
CREATE PROCEDURE statement, 21, 160, 170-171
stored, 4
(see also stored procedures)
processing
dynamic result sets, 282, 296
iterative, loops, 93-98
loops, 555-559
result sets
JDBC, 321
Python, 372
SELECT statements, JDBC, 312
SQL, 445-449
program construction, 569-577
programming languages, applying stored programs, 4
programming, pair, 536
programs
checking types in Java, 73
(see also stored programs)
properties, transactions, 201-203
Python, 261, 364-385
connections, 365
error handling, 153
example of, 380-385
exception handling, 366
metadata, 371
output parameters, 379
queries, retrieving rows from, 368
result sets
dynamically processing, 372
retrieving, 374
retrieving dynamic, 376
retrieving multiple, 375
statements
executing, 367
passing parameters to, 367
stored programs
applying, 380-385
MySQLdb extension, 364-379
transaction management, 370
transactions, managing, 370
Q[ Top ]
QUARTER function, 233
queries
caches, 446
DataSets, 395-397
Hibernate, 335
one-off, issuing, 292
Perl, issuing, 348
result sets, retrieving, 278
rows, retrieving, 368-370
SELECT INTO syntax, 33-37
slow query logs, 458
stored procedures, mapping, 335
subqueries, tuning, 486-493
query( ) method, 285
PDO, 292
Query Browser
procedures, creating, 20-25
stored programs, creating, 161
QUOTE function, 217
R[ Top ]
RADIANS function, 223
RaiseError attribute, 346
RAND function, 221
Ravenscroft, Anna, 372
Read( ) method, 393
READ COMMITTED isolation level, 181
READ UNCOMMITTED isolation level, 181
readability, code, 561
recursion, optimizing, 526-527
reducing
commit frequency, optimizing DML, 504
sorts, 502
traffic, 265, 509, 512-515
redundancy
code, avoiding, 558
conditions, 88
ref column in EXPLAIN statement, 453
referencing views, 499
REGEXP operator, 62
registering
Connector/Net drivers, 387-390
JDBC drivers, 310
OUT variables, 319
parameters, 270
relative cardinality (of indexes), 449
RELEASE_LOCK function, 236
reliability of stored programs, xv
REPEAT function, 215
REPEAT loops, 95
cursors, 105
LEAVE statements, 107
REPEAT UNTIL loops, 8
REPEATABLE READ isolation level, 182
REPLACE function, 215
replacing expressions with functions, 546
resetting NOT FOUND conditions, 564
resisting code injection, 437
resources
Internet, 14
stored programs, 13-15
restarting execution of loops, 95
restricting access, 430
result( ) method, 288
result sets
DataSets, 395-397
dynamic
ADO.NET, 405-408
Perl, 356
processing, 282, 296
multiple
ADO.NET, 404
handling, 299
Perl, 355
PHP, retrieving from, 114-117
processing
JDBC, 321
Python, 372
retrieving, 272
dynamic, 376
JDBC, 312, 320
metadata, 282, 295, 313, 352
multiple, 287
Perl, 348
prepared statements, 281
Python, 371
queries, 278
stored procedures, 117, 286
stored functions, 242
stored procedures, returning from, 36
retrieving
database information, 173-175
metadata
DataReader, 394
Python, 371
output parameters, 272
ADO.NET, 410-412
values, 322
result sets, 272
dynamic, 376
JDBC, 312, 320
metadata, 282, 295, 313, 352
multiple, 287
Perl, 348
from PHP, 114-117
prepared statements, 281
queries, 278
stored procedures, 117, 286
rows, 349-352
from queries, 368-370
RETURN statements, creating stored functions, 239
returning result sets from stored procedures, 36
reusing
statement objects, 391
statements, 348
REVERSE function, 217
reviewing code, 534
rewriting subqueries as joins, 488
RIGHT function, 217
rollback( ) method, 280, 370
ROLLBACK statement, 183, 292, 353
ROLLBACK TO SAVEPOINT statement, 183
rollbacks
avoiding, 202
managing, 370
transactions, 202
rolling back AUDIT_LOG tables, 187
ROUND function, 222
routines, stored programs, 267
rows
columns, in EXPLAIN statement, 453
cursors, fetching from, 103
DictCursor, 369
last row conditions, error handling, 128-129
locking, 195
queries, retrieving, 368-370
retrieving, 349-352
RPAD function, 215
RTRIM function, 215
rules, naming variables, 52
S[ Top ]
SAVEPOINT statement, 183
savepoints, 185-190, 563
avoiding, 202
scalars, 68
scanning
full table scans, 464
tables
avoiding accidental, 472-475
avoiding scans, 578
optimizing, 475-478
SCCS (source code control system), 539
Schneider, Robert D., 14
scope
condition handlers, 137
variables, controlling, 78
searching
CASE statements, 90
indexes, 473
prime numbers, 512
SECOND function, 225, 233
SEC_TO_TIME function, 230
security
best practices, 568
databases, stored programs, 264
policies, implementing, 427-431
SQL injection, 123
stored programs
code injection, 434-440
execution mode options, 423-434
privileges, 422
SELECT FOR UPDATE statement, 564
SELECT INTO syntax, 33-37
SELECT statement
ADO.NET, issuing, 393
code injection, 435
cursors, creating, 101-112
EXISTS subqueries, 487
INTO clause, 100
JDBC, 312
stored procedures, returning, 36
subqueries, 486
unbounded, applying, 112-118
selecting
development tools, 534
indexes, 466-469, 471
selective indexes, 449
select_type column in EXPLAIN statement, 451
self-documentation, comments, 543
self-joins, avoiding, 515
semicolons in code, 165
sequences, Fibonacci, 526
SERIALIZABLE isolation level, 182
servers
applications, stored programs, 261
traffic, reducing, 509
server-side prepared statements, 118-123, 314
servlets, 323
stored programs, applying, 324-328
(see also Java)
sessions, isolation levels, 181-182
SESSION_USER function, 236
SET AUTOCOMMIT statement, 184, 292
SET data types, 70
SET statement, 27
functions, 67
operators, 60
variables, assigning values to, 53
SET TRANSACTION statement, 183
setAutocommit( ) method, 315
setting values, triggers, 45
setUpdate( ) method, 315
SHA function, 217
SHA1 function, 217
SHOW CREATE FUNCTION statement, 174
SHOW CREATE PROCEDURE statement, 174
SHOW FUNCTION STATUS statement, 174
SHOW PROCEDURE STATUS statement, 174
SHOW STATUS statement, 456
statistics, 458
side-effects (in programs), avoiding, 573
SIGN function, 222
SIGNAL statement
emulating, 143
exceptions, creating, 141
SIN function, 223
single rows, fetching from cursors, 103
single-line (- -) comments, 60
single-sweep multi-joins, 480
sizing
tables, 444
transactions, 202, 563
Sleepycat Software, 6
slow query logs, 458
SORT function, 222
sorts
disk-based, overhead, 501
indexes, creating to avoid, 501
memory, increasing, 502
overhead, reducing, 502
reducing, 502
SOUNDEX function, 217
source code control system (SCCS), 539
SOURCE statement, 161
SPACE function, 217
speed, stored programs, 509-512
Spring, xiii
stored procedures, 337-342
SQL clause, 240
SQL SECURITY {DEFINER|INVOKER} keyword, 171
SQL (Structured Query Language)
caching, 446
dynamic, best practices, 566-569
error handling, 123
injection, 123
integration, 7
measuring, 456
non-SELECT SQL statements, applying in stored programs, 99-100
optimizer hints, 455
parsing, 445
prepared statements, 118-123
processing, 445-449
server-side prepared statements, 118-123
statements
managing stored programs, 170-173
MySQLdb extensions, 367
stored functions, 242
tuning, 449-459
stored programs
best practices, 562-566
types of, 4
tuning, 443, 578
anti-joins, 493-496
DML, 503-506
examples of, 459-461
FROM clause, 495-500
GROUP BY clause, 501-502
joins, 480-484
ORDER clause, 501-502
subqueries, 486-493
table access, 463-480
SQL:2003 specification, missing features, 139-145
SQLCODE, 140
SQLExceptions, throwing, 316
SQLSTATE code, 133
accessing, 140
error handling, 134
named conditions, 139
stack traces, 317
standardizing modules, 571
START TRANSACTION statement, 182, 184, 292
starting
loops, returning to, 95
transactions, 562
statements
ALTER PROCEDURE/ FUNCTION, 173
ANALYZE TABLE, 449
BEGIN, 77
CALL, 38-39
CASE, 8, 88-92
comparing IF statements to, 92
formatting, 580
inclusive, 553
optimizing, 522-526
stored functions, 245
CLOSE, cursors, 102
COMMIT, 182, 292, 353
conditional control, 82-93
conditional logic, 8
CONTINUE, avoiding loop processing, 520
CREATE FUNCTION, 21, 171
CREATE PROCEDURE, 21, 160, 170-171
syntax, 170
CREATE STATEMENT, 238
CREATE TRIGGER, 21, 172, 249
cursors, 102
DEALLOCATE, 119
DECLARE, 7, 25, 26, 50
defining cursors, 101
DECLARE CONTINUE HANDLER, 127
DECLARE HANDLER, 129
DELIMITER, 166
DROP, 173
DROP PROCEDURE, 160
END, 77
END LOOP, 93
EXECUTE
PREPARE statement, 118
privileges, 422
EXPLAIN, 449-459
EXPLAIN EXTENDED, 454
FETCH, cursors, 102
GRANT, 423
IF, 8, 82-88
formatting, 580
functions, 67
optimizing, 522-526
tuning, 524
IF-THEN-ELSE, 85, 86
INSERT, triggers, 44
ITERATE, 95
LEAVE, 94
avoiding loop processing, 520
block labels, 81
loops, 557
REPEAT loops, 107
LOCK TABLES, 183
LOOP, 93
loop-invariant, 519
MySQL START TRANSACTION, 353
non-SELECT
ADO.NET, 390, 393
embedding, 37
JDBC, 312
mysqli extensions, 278
PDO, 290
objects, reusing, 391
one-off, issuing, 347
OPEN, cursors, 102
overhead, 91
PREPARE, 118
prepared
bind variables, 348
binding parameters, 294
JDBC, 313
mysqli extensions, 280
PDO, 293
retrieving result sets, 281
RETURN, creating stored functions, 239
reusing, 348
ROLLBACK, 183, 292, 353
ROLLBACK TO SAVEPOINT, 183
SAVEPOINT, 183
SELECT
ADO.NET, 393
applying unbounded, 112-118
code injection, 435
creating cursors, 101-112
EXISTs subqueries, 487
INTO clause, 100
JDBC, 312
returning from stored procedures, 36
subqueries, 486
SELECT FOR UPDATE, 564
server-side prepared, 118-123
SET, 27
assigning values to variables, 53
functions, 67
operators, 60
SET AUTOCOMMIT, 184, 292
SET TRANSACTION, 183
SHOW CREATE FUNCTION, 174
SHOW CREATE PROCEDURE, 174
SHOW FUNCTION STATUS, 174
SHOW PROCEDURE STATUS, 174
SHOW STATUS, 456
statistics, 458
SIGNAL
creating exceptions, 141
emulating, 143
SOUCE, 161
SQL
managing stored programs, 170-173
MySQLdb extensions, 367
stored functions, 242
tuning, 449-459
START TRANSACTION, 182, 184, 292
transaction management, 182
UPDATE
embedding, 38
optimizing correlated updates, 517
statistics
indexes, viewing, 448
InnoDB, 456
Java, generating, 513
SHOW STATUS statement, 458
tables, 448
stored functions, 4, 6, 9
ADO.NET, calling, 412
applying, 244-248
best practices, 565
calling, 242-244, 273
creating, 238-242
SQL statements in, 242
tutorial, 41-43
(see also stored programs)
stored procedures, 4, 6
access, restricting, 430
ADO.NET
calling, 401
input parameters, 402
multiple result sets, 404
authorization, 430
code injection, 438
cursors, defining, 102
deadlocks, 193
error handling
applying, 145-150
last row conditions, 128-129
overview of, 125-128
Hibernate, 332-337
invoker rights security, 426, 433
JDBC, executing, 320
multiple result sets, retrieving, 287
output parameters, handling, 287
persistence, 336
result sets
retrieving, 117, 286
returning, 36
Spring, 337-342
SQL injection, 123
tables, updating, 120
tutorial, 20
calling from PHP, 45
calling stored programs, 38-39
conditional execution, 30
creating procedures, 21-23
creating procedures using MySQL Query Browsers, 20-25
database interactions, 33
error handling, 32
loops, 31
parameters, 27-29
SELECT INTO syntax, 33-37
variables, 25, 27
stored programs
abstraction, 265
ADO.NET
applying, 401-412
DataReader, 403
DataSets, 408-410
overview of, 386-401
advice for developers, 15-19
applications, 261-264
advantages of, 264-268
calling, 270-273
disadvantages of, 268-269
appropriate use of, xv
ASP.NET, applying, 413-418
best practices
coding style and conventions, 539-544
conditional logic, 551-554
development process, 533-539
dynamic SQL, 566-569
exception handling, 559-562
loop processing, 555-559
performance, 577-581
program construction, 569-577
SQL, 562-566
variables, 544-551
blocks, 77-82
built-in functions, 64-68, 205-237
calling, 38-39
closing, 273
comments, 60
conditional control statements, 82-93
controls, 8
creating, 159-166
avoiding invalid data assignments, 550
granting privileges to, 422
data types, 68-71
database security, 264
editing, 159
existing programs, 166-168
System Editor, 160
error handling, 10
error handling in calling applications, 150-155
executing, 270, 272
granting privileges to, 423
EXPLAIN statements, 451
expressions, 64
input parameters, binding, 299
Java, 310-342
J2EE applications, 323-332
JDBC, applying, 317-323
JDBC, overview of, 310-317
languages (see specific languages)
managing, 170-173
MySQLdb, applying, 373-379
.NET, 386-418
non-SELECT SQL statements, applying in, 99-100
operators, 60
bitwise, 63
comparison, 61
logical, 62-63
mathematical, 61
optimizing, 508
CASE statements, 522-526
cursors, 528
IF statements, 522-526
loops, 519-522
recursion, 526-527
reducing traffic, 512-515
speed, 509-512
SQL statements, 515-519
trigger overhead, 529-531
overview of, 3-7
Perl, 343-363
DBD::mysql drivers, 344-354
executing with DBD::mysql drivers, 354-361
PHP, 275-308
mysqli extension, 276-289
options, 276
PDO, 289-307
Python, 364-385
applying, 380-385
MySQLdb extension, 364-379
reasons for using, 4
resources, 13-15
savepoints, 189
security
code injection, 434-440
execution mode options, 423-434
permissions, 422
semicolons in code, 165
SQL, embedding, 7
SQL tuning, 443
anti-joins, 493-496
DML, 503-506
examples of, 459-461
FROM clause, 495-500
GROUP BY clause, 501-502
joins, 480-484
ORDER clause, 501-502
statements, 449-459
subqueries, 486-493
table access, 463-480
strict mode, 72-76
tuning, 508
viewing, 431
(see also stored functions, stored procedures)
StoredProcedure class, 341
store_result( ) method, 288
strategies, locking, 197
STRCMP function, 215
strict mode, 72-76
string data types, 68
string functions (see functions, built-in)
string literals, 52
STR_TO_DATE function, 230
structure of stored programs, 77-82
Structured Query Language (see SQL)
styles, code, 539-544
SUBDATE function, 233
subqueries
complex joins, 490
FROM clause, 495-500
joins, rewriting as, 488
SQL tuning, 486-493
substitution variables, 348
SUBSTRING_INDEX function, 217
substrings, suppressing indexes, 473
SUBSTRNG function, 215
SUBTIME function, 233
subtraction (-) operator, 61
suppressing indexes
functions, 473
substrings, 473
SYSDATE function, 233
System Editor, creating stored programs, 160
T[ Top ]
tables
access, SQL tuning, 463-480
AUDIT_LOG, inserting/rolling back, 187
caches, 446
columns, in EXPLAIN statement, 452
direct access, 430
INFORMATION_SCHEMA.ROUTINES, 174
joins, 480
merging, 478-480
partitioning, 478-480
scanning
avoiding accidental, 472-475
avoiding scans, 578
full table scans, 464
optimizing, 475-478
sizing, 444
statistics, 448
temporary, creating, 117
truth
AND operator, 62
OR operator, 63
XOR operator, 63
updating, 120
TAN function, 223
TEMPFILE algorithm, 498
templates, standardizing modules, 571
temporary tables, creating, 117
terminating loops, 94, 555
testing, 76
code, 534
conditions, 522, 552
TEXT data types, 71
text editors, creating stored programs, 161
THEN clause, 84
third-party editing tools, creating stored programs, 163
throwing SQLExceptions, 316
time data types, 71
TIMEDIFF function, 231
timeouts, locks, 196
TIMESTAMP function, 231
TIMESTAMPADD function, 231
TIMESTAMPDIFF function, 231
TIME_TO_SEC function, 230
Toad, editing stored programs, 165
TO_DAYS function, 233
tools
Mytrace.pl, 461
third-party editing, creating stored programs, 163
traffic
networks, 265
reducing, 509, 512-515
transactions
dangling, 562
defining, 183
design, 201-203
isolation levels, 181-182
JDBC, 315
locks, 190-201
managing
ADO.NET, 398-401
C#, 398
mysqli extensions, 279
overhead, 180
PDO, 292
Perl, 353
Python, 370
statements, 182
VB.NET, 400
savepoints, 185-190
sizing, 202, 563
starting, 562
support, 180
Transact-SQL language (SQL Server), 7, 49, 262
triggers, 4, 6, 12
actions, 250
applying, 251-256
check constraint, 254
column values, 250
CREATE TRIGGER statement, 21
creating, 249-251
data, validating, 254-256
DML performance, 506
indexes, 531
multiple, 576
overhead, 256, 529
tutorial, 43-45
(see also stored programs)
TRIM function, 216
troubleshooting
stored procedures, 32
stored programs, 10
TRUE, IF statements, 83
truth tables
AND operator, 62
OR operator, 63
XOR operator, 63
try blocks, 370
tuning (SQL), 443, 578
anti-joins, 493-496
DML, 503-506
examples of, 459-461
FROM clause, 495-500
GROUP BY clause, 501-502
IF statements, 524
joins, 480-484
ORDER clause, 501-502
statements, 449-459
stored programs, 508
subqueries, 486-493
table access, 463-480
(see also optimizing)
tutorials
stored functions, 41-43
stored procedures, 20
calling stored programs, 38-39
conditional execution, 30
creating procedures, 21-23
creating procedures using MySQL Query Browsers, 20-25
database interactions, 33
error handling, 32
loops, 31
parameters, 27-29
PHP, calling from, 45
SELECT INTO syntax, 33-37
variables, 25, 27
triggers, 43-45
types
of built-in functions, 64-68
of columns in EXPLAIN statement, 452
of comments, 60
of condition handlers, 130
of cursor loops, 105-108
of data types, 68-71
of expressions, 64
of literals, 51
of loops, 31
of stored programs, 4
U[ Top ]
UCASE function, 216
UDFs (user-defined functions), 244
unbounded SELECT statements, applying, 112-118
UNCOMPRESS function, 218
UNCOMPRESSED_LENGTH function, 217
UNHEX function, 218
Unireg, 5
Unix, xiii
installing DBD::mysql drivers, 344
unnamed views, 495
UNTIL loops, 95
UPDATE statements
correlated updates, optimizing, 517
embedding, 38
updating
Hibernate, 337
SELECT FOR UPDATE statement, 564
UPPER function, 218
USE INDEX hint, 466
USER function, 236
user variables, 57
user-defined functions (see UDFs)
utilities, MyTrace.pl, 461
UUID function, 236
V[ Top ]
validating
code injection, binding parameters, 437
correct behavior, 76
data (with triggers), 254-256
parameter values, 567
values
columns
accessing, 369
triggers, 250
literals, 51-60
NULL, 50
output parameters, retrieving, 322
parameters, validating, 567
triggers, setting, 45
variables, 49-51
assigning, 52
binding, 566
(see also NULL values)
variables, 25, 27
best practices, 544-551
bind, Perl, 348
creating, 50
declaring, 51
deleting, 547
dynamic typing (in PHP), 74
global, 549
languages, 49-51
naming, 52, 545
OUT, registering, 319
output, Perl, 357
overloading, 80
overriding, 80, 546
scope, controlling, 78
SELECT INTO syntax, 33-37
stored procedures, defining cursors, 102
user, 57
values
assigning, 52
binding, 566
VB.NET
connecting to MySQL, 389
DataReader, applying, 394
DataSets, populating, 396
dynamic result sets, 407
error handling, 155, 397
multiple result sets, 405
output parameters, 410
parameters, applying, 391
stored functions, 412
stored procedures
calling, 401
calling input parameters, 403
DataReader, 403
transaction management, 400
VERSION function, 236
vertical code alignment, 542
viewing
index statistics, 448
stored procedures, 430
stored programs, 431
table statistics, 448
views
applying, 498
referencing, 499
W[ Top ]
walkthroughs, code, 536
Wall, Larry, 343
web sites, resources, xiii
WEEK function, 231
WEEKDAY function, 232
WEEKOFYEAR function, 233
WHILE loops, 8, 96
WHILE-END WHILE loops, 107
Widenius, Michael "Monty", 5
Williams, Hugh, Lane, David, 13
Windows, installing DBD::mysql drivers, 345
Y[ Top ]
YEAR function, 225, 232
YEARWEEK function, 232
Zurück zu MySQL Stored Procedure Programming
