~maria-captains/maria/mysql-6.0-backup

616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161

186

D18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18F

187

61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F

188

D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61

189

190

select length(s1),char_length(s1) from t1;

191

length(s1) char_length(s1)

192

255 255

193

254 127

194

255 170

195

255 170

196

254 127

197

drop table t1;

198

create table t1 (s1 text character set utf8mb4);

199

insert into t1 select repeat('a',66000);

200

Warnings:

201

Warning 1265 Data truncated for column 's1' at row 1

202

insert into t1 select repeat('�',66000);

203

Warnings:

204

Warning 1265 Data truncated for column 's1' at row 1

205

insert into t1 select repeat('a�',66000);

206

Warnings:

207

Warning 1265 Data truncated for column 's1' at row 1

208

insert into t1 select repeat('�a',66000);

209

Warnings:

210

Warning 1265 Data truncated for column 's1' at row 1

211

insert into t1 select repeat('��',66000);

212

Warnings:

213

Warning 1265 Data truncated for column 's1' at row 1

214

select length(s1),char_length(s1) from t1;

215

length(s1) char_length(s1)

216

65535 65535

217

65534 32767

218

65535 43690

219

65535 43690

220

65534 32767

221

drop table t1;

222

create table t1 (s1 char(10) character set utf8mb4);

223

insert into t1 values (0x41FF);

224

Warnings:

225

Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1

226

select hex(s1) from t1;

227

hex(s1)

228

229

drop table t1;

230

create table t1 (s1 varchar(10) character set utf8mb4);

231

insert into t1 values (0x41FF);

232

Warnings:

233

Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1

234

select hex(s1) from t1;

235

hex(s1)

236

237

drop table t1;

238

create table t1 (s1 text character set utf8mb4);

239

insert into t1 values (0x41FF);

240

Warnings:

241

Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1

242

select hex(s1) from t1;

243

hex(s1)

244

245

drop table t1;

246

create table t1 (a text character set utf8mb4, primary key(a(371)));

247

ERROR 42000: Specified key was too long; max key length is 1332 bytes

248

CREATE TABLE t1 ( a varchar(10) ) CHARACTER SET utf8mb4;

249

INSERT INTO t1 VALUES ( 'test' );

250

SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a;

251

a a

252

test test

253

SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = 'test' and b.a = 'test';

254

a a

255

test test

256

SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a and a.a = 'test';

257

a a

258

test test

259

DROP TABLE t1;

260

create table t1 (a char(255) character set utf8mb4);

261

insert into t1 values('b'),('b');

262

select * from t1 where a = 'b';

263

264

265

266

select * from t1 where a = 'b' and a = 'b';

267

268

269

270

select * from t1 where a = 'b' and a != 'b';

271

272

drop table t1;

273

set collation_connection=utf8mb4_general_ci;

274

drop table if exists t1;

275

create table t1 as

276

select repeat(' ', 64) as s1, repeat(' ',64) as s2

277

union

278

select null, null;

279

show create table t1;

280

Table Create Table

281

t1 CREATE TABLE `t1` (

282

`s1` varchar(64) CHARACTER SET utf8mb4 DEFAULT NULL,

283

`s2` varchar(64) CHARACTER SET utf8mb4 DEFAULT NULL

284

) ENGINE=MyISAM DEFAULT CHARSET=latin1

285

delete from t1;

286

insert into t1 values('aaa','aaa');

287

insert into t1 values('aaa|qqq','qqq');

288

insert into t1 values('gheis','^[^a-dXYZ]+$');

289

insert into t1 values('aab','^aa?b');

290

insert into t1 values('Baaan','^Ba*n');

291

insert into t1 values('aaa','qqq|aaa');

292

insert into t1 values('qqq','qqq|aaa');

293

insert into t1 values('bbb','qqq|aaa');

294

insert into t1 values('bbb','qqq');

295

insert into t1 values('aaa','aba');

296

insert into t1 values(null,'abc');

297

insert into t1 values('def',null);

298

insert into t1 values(null,null);

299

insert into t1 values('ghi','ghi[');

300

select HIGH_PRIORITY s1 regexp s2 from t1;

301

s1 regexp s2

302

303

304

305

306

307

308

309

310

311

312

NULL

313

NULL

314

NULL

315

NULL

316

drop table t1;

317

set names utf8mb4;

318

set names utf8mb4;

319

select 'вася' rlike '[[:<:]]вася[[:>:]]';

320

'вася' rlike '[[:<:]]вася[[:>:]]'

321

322

select 'вася ' rlike '[[:<:]]вася[[:>:]]';

323

'вася ' rlike '[[:<:]]вася[[:>:]]'

324

325

select ' вася' rlike '[[:<:]]вася[[:>:]]';

326

' вася' rlike '[[:<:]]вася[[:>:]]'

327

328

select ' вася ' rlike '[[:<:]]вася[[:>:]]';

329

' вася ' rlike '[[:<:]]вася[[:>:]]'

330

331

select 'васяz' rlike '[[:<:]]вася[[:>:]]';

332

'васяz' rlike '[[:<:]]вася[[:>:]]'

333

334

select 'zвася' rlike '[[:<:]]вася[[:>:]]';

335

'zвася' rlike '[[:<:]]вася[[:>:]]'

336

337

select 'zвасяz' rlike '[[:<:]]вася[[:>:]]';

338

'zвасяz' rlike '[[:<:]]вася[[:>:]]'

339

340

CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8mb4_unicode_ci);

341

ALTER TABLE t1 ADD COLUMN b CHAR(20);

342

DROP TABLE t1;

343

set names utf8mb4;

344

create table t1 (a enum('aaaa','проба') character set utf8mb4);

345

show create table t1;

346

Table Create Table

347

t1 CREATE TABLE `t1` (

348

`a` enum('aaaa','проба') CHARACTER SET utf8mb4 DEFAULT NULL

349

) ENGINE=MyISAM DEFAULT CHARSET=latin1

350

insert into t1 values ('проба');

351

select * from t1;

352

353

проба

354

create table t2 select ifnull(a,a) from t1;

355

show create table t2;

356

Table Create Table

357

t2 CREATE TABLE `t2` (

358

`ifnull(a,a)` varchar(5) CHARACTER SET utf8mb4 DEFAULT NULL

359

) ENGINE=MyISAM DEFAULT CHARSET=latin1

360

select * from t2;

361

ifnull(a,a)

362

проба

363

drop table t1;

364

drop table t2;

365

create table t1 (c varchar(30) character set utf8mb4, unique(c(10)));

366

insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');

367

insert into t1 values ('aaaaaaaaaa');

368

insert into t1 values ('aaaaaaaaaaa');

369

ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'

370

insert into t1 values ('aaaaaaaaaaaa');

371

ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'

372

insert into t1 values (repeat('b',20));

373

select c c1 from t1 where c='1';

374

375

376

select c c2 from t1 where c='2';

377

378

379

select c c3 from t1 where c='3';

380

381

382

select c cx from t1 where c='x';

383

384

385

select c cy from t1 where c='y';

386

387

388

select c cz from t1 where c='z';

389

390

391

select c ca10 from t1 where c='aaaaaaaaaa';

392

ca10

393

aaaaaaaaaa

394

select c cb20 from t1 where c=repeat('b',20);

395

cb20

396

bbbbbbbbbbbbbbbbbbbb

397

drop table t1;

398

create table t1 (c varchar(30) character set utf8mb4, unique(c(10))) engine=innodb;

399

insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');

400

insert into t1 values ('aaaaaaaaaa');

401

insert into t1 values ('aaaaaaaaaaa');

402

ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'

403

insert into t1 values ('aaaaaaaaaaaa');

404

ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'

405

insert into t1 values (repeat('b',20));

406

select c c1 from t1 where c='1';

407

408

409

select c c2 from t1 where c='2';

410

411

412

select c c3 from t1 where c='3';

413

414

415

select c cx from t1 where c='x';

416

417

418

select c cy from t1 where c='y';

419

420

421

select c cz from t1 where c='z';

422

423

424

select c ca10 from t1 where c='aaaaaaaaaa';

425

ca10

426

aaaaaaaaaa

427

select c cb20 from t1 where c=repeat('b',20);

428

cb20

429

bbbbbbbbbbbbbbbbbbbb

430

drop table t1;

431

create table t1 (c char(3) character set utf8mb4, unique (c(2)));

432

insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');

433

insert into t1 values ('a');

434

insert into t1 values ('aa');

435

insert into t1 values ('aaa');

436

ERROR 23000: Duplicate entry 'aa' for key 'c'

437

insert into t1 values ('b');

438

insert into t1 values ('bb');

439

insert into t1 values ('bbb');

440

ERROR 23000: Duplicate entry 'bb' for key 'c'

441

insert into t1 values ('а');

442

insert into t1 values ('аа');

443

insert into t1 values ('ааа');

444

ERROR 23000: Duplicate entry 'аа' for key 'c'

445

insert into t1 values ('б');

446

insert into t1 values ('бб');

447

insert into t1 values ('ббб');

448

ERROR 23000: Duplicate entry 'бб' for key 'c'

449

insert into t1 values ('ꪪ');

450

insert into t1 values ('ꪪꪪ');

451

insert into t1 values ('ꪪꪪꪪ');

452

ERROR 23000: Duplicate entry 'ꪪꪪ' for key 'c'

453

drop table t1;

454

create table t1 (c char(3) character set utf8mb4, unique (c(2))) engine=innodb;

455

insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');

456

insert into t1 values ('a');

457

insert into t1 values ('aa');

458

insert into t1 values ('aaa');

459

ERROR 23000: Duplicate entry 'aa' for key 'c'

460

insert into t1 values ('b');

461

insert into t1 values ('bb');

462

insert into t1 values ('bbb');

463

ERROR 23000: Duplicate entry 'bb' for key 'c'

464

insert into t1 values ('а');

465

insert into t1 values ('аа');

466

insert into t1 values ('ааа');

467

ERROR 23000: Duplicate entry 'аа' for key 'c'

468

insert into t1 values ('б');

469

insert into t1 values ('бб');

470

insert into t1 values ('ббб');

471

ERROR 23000: Duplicate entry 'бб' for key 'c'

472

insert into t1 values ('ꪪ');

473

insert into t1 values ('ꪪꪪ');

474

insert into t1 values ('ꪪꪪꪪ');

475

ERROR 23000: Duplicate entry 'ꪪꪪ' for key 'c'

476

drop table t1;

477

create table t1 (

478

c char(10) character set utf8mb4,

479

unique key a using hash (c(1))

480

) engine=heap;

481

show create table t1;

482

Table Create Table

483

t1 CREATE TABLE `t1` (

484

`c` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,

485

UNIQUE KEY `a` (`c`(1)) USING HASH

486

) ENGINE=MEMORY DEFAULT CHARSET=latin1

487

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

488

insert into t1 values ('aa');

489

ERROR 23000: Duplicate entry 'a' for key 'a'

490

insert into t1 values ('aaa');

491

ERROR 23000: Duplicate entry 'a' for key 'a'

492

insert into t1 values ('б');

493

insert into t1 values ('бб');

494

ERROR 23000: Duplicate entry 'б' for key 'a'

495

insert into t1 values ('ббб');

496

ERROR 23000: Duplicate entry 'б' for key 'a'

497

select c as c_all from t1 order by c;

498

c_all

499

500

501

502

503

504

505

506

select c as c_a from t1 where c='a';

507

c_a

508

509

select c as c_a from t1 where c='б';

510

c_a

511

512

drop table t1;

513

create table t1 (

514

c char(10) character set utf8mb4,

515

unique key a using btree (c(1))

516

) engine=heap;

517

show create table t1;

518

Table Create Table

519

t1 CREATE TABLE `t1` (

520

`c` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,

521

UNIQUE KEY `a` (`c`(1)) USING BTREE

522

) ENGINE=MEMORY DEFAULT CHARSET=latin1

523

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

524

insert into t1 values ('aa');

525

ERROR 23000: Duplicate entry 'a' for key 'a'

526

insert into t1 values ('aaa');

527

ERROR 23000: Duplicate entry 'a' for key 'a'

528

insert into t1 values ('б');

529

insert into t1 values ('бб');

530

ERROR 23000: Duplicate entry 'б' for key 'a'

531

insert into t1 values ('ббб');

532

ERROR 23000: Duplicate entry 'б' for key 'a'

533

select c as c_all from t1 order by c;

534

c_all

535

536

537

538

539

540

541

542

select c as c_a from t1 where c='a';

543

c_a

544

545

select c as c_a from t1 where c='б';

546

c_a

547

548

drop table t1;

549

create table t1 (

550

c char(10) character set utf8mb4,

551

unique key a (c(1))

552

) engine=innodb;

553

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

554

insert into t1 values ('aa');

555

ERROR 23000: Duplicate entry 'a' for key 'a'

556

insert into t1 values ('aaa');

557

ERROR 23000: Duplicate entry 'a' for key 'a'

558

insert into t1 values ('б');

559

insert into t1 values ('бб');

560

ERROR 23000: Duplicate entry 'б' for key 'a'

561

insert into t1 values ('ббб');

562

ERROR 23000: Duplicate entry 'б' for key 'a'

563

select c as c_all from t1 order by c;

564

c_all

565

566

567

568

569

570

571

572

select c as c_a from t1 where c='a';

573

c_a

574

575

select c as c_a from t1 where c='б';

576

c_a

577

578

drop table t1;

579

create table t1 (c varchar(30) character set utf8mb4 collate utf8mb4_bin, unique(c(10)));

580

insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');

581

insert into t1 values ('aaaaaaaaaa');

582

insert into t1 values ('aaaaaaaaaaa');

583

ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'

584

insert into t1 values ('aaaaaaaaaaaa');

585

ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c'

586

insert into t1 values (repeat('b',20));

587

select c c1 from t1 where c='1';

588

589

590

select c c2 from t1 where c='2';

591

592

593

select c c3 from t1 where c='3';

594

595

596

select c cx from t1 where c='x';

597

598

599

select c cy from t1 where c='y';

600

601

602

select c cz from t1 where c='z';

603

604

605

select c ca10 from t1 where c='aaaaaaaaaa';

606

ca10

607

aaaaaaaaaa

608

select c cb20 from t1 where c=repeat('b',20);

609

cb20

610

bbbbbbbbbbbbbbbbbbbb

611

drop table t1;

612

create table t1 (c char(3) character set utf8mb4 collate utf8mb4_bin, unique (c(2)));

613

insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');

614

insert into t1 values ('a');

615

insert into t1 values ('aa');

616

insert into t1 values ('aaa');

617

ERROR 23000: Duplicate entry 'aa' for key 'c'

618

insert into t1 values ('b');

619

insert into t1 values ('bb');

620

insert into t1 values ('bbb');

621

ERROR 23000: Duplicate entry 'bb' for key 'c'

622

insert into t1 values ('а');

623

insert into t1 values ('аа');

624

insert into t1 values ('ааа');

625

ERROR 23000: Duplicate entry 'аа' for key 'c'

626

insert into t1 values ('б');

627

insert into t1 values ('бб');

628

insert into t1 values ('ббб');

629

ERROR 23000: Duplicate entry 'бб' for key 'c'

630

insert into t1 values ('ꪪ');

631

insert into t1 values ('ꪪꪪ');

632

insert into t1 values ('ꪪꪪꪪ');

633

ERROR 23000: Duplicate entry 'ꪪꪪ' for key 'c'

634

drop table t1;

635

create table t1 (

636

c char(10) character set utf8mb4 collate utf8mb4_bin,

637

unique key a using hash (c(1))

638

) engine=heap;

639

show create table t1;

640

Table Create Table

641

t1 CREATE TABLE `t1` (

642

`c` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,

643

UNIQUE KEY `a` (`c`(1)) USING HASH

644

) ENGINE=MEMORY DEFAULT CHARSET=latin1

645

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

646

insert into t1 values ('aa');

647

ERROR 23000: Duplicate entry 'a' for key 'a'

648

insert into t1 values ('aaa');

649

ERROR 23000: Duplicate entry 'a' for key 'a'

650

insert into t1 values ('б');

651

insert into t1 values ('бб');

652

ERROR 23000: Duplicate entry 'б' for key 'a'

653

insert into t1 values ('ббб');

654

ERROR 23000: Duplicate entry 'б' for key 'a'

655

select c as c_all from t1 order by c;

656

c_all

657

658

659

660

661

662

663

664

select c as c_a from t1 where c='a';

665

c_a

666

667

select c as c_a from t1 where c='б';

668

c_a

669

670

drop table t1;

671

create table t1 (

672

c char(10) character set utf8mb4 collate utf8mb4_bin,

673

unique key a using btree (c(1))

674

) engine=heap;

675

show create table t1;

676

Table Create Table

677

t1 CREATE TABLE `t1` (

678

`c` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,

679

UNIQUE KEY `a` (`c`(1)) USING BTREE

680

) ENGINE=MEMORY DEFAULT CHARSET=latin1

681

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

682

insert into t1 values ('aa');

683

ERROR 23000: Duplicate entry 'a' for key 'a'

684

insert into t1 values ('aaa');

685

ERROR 23000: Duplicate entry 'a' for key 'a'

686

insert into t1 values ('б');

687

insert into t1 values ('бб');

688

ERROR 23000: Duplicate entry 'б' for key 'a'

689

insert into t1 values ('ббб');

690

ERROR 23000: Duplicate entry 'б' for key 'a'

691

select c as c_all from t1 order by c;

692

c_all

693

694

695

696

697

698

699

700

select c as c_a from t1 where c='a';

701

c_a

702

703

select c as c_a from t1 where c='б';

704

c_a

705

706

drop table t1;

707

create table t1 (

708

c char(10) character set utf8mb4 collate utf8mb4_bin,

709

unique key a (c(1))

710

) engine=innodb;

711

insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');

712

insert into t1 values ('aa');

713

ERROR 23000: Duplicate entry 'a' for key 'a'

714

insert into t1 values ('aaa');

715

ERROR 23000: Duplicate entry 'a' for key 'a'

716

insert into t1 values ('б');

717

insert into t1 values ('бб');

718

ERROR 23000: Duplicate entry 'б' for key 'a'

719

insert into t1 values ('ббб');

720

ERROR 23000: Duplicate entry 'б' for key 'a'

721

select c as c_all from t1 order by c;

722

c_all

723

724

725

726

727

728

729

730

select c as c_a from t1 where c='a';

731

c_a

732

733

select c as c_a from t1 where c='б';

734

c_a

735

736

drop table t1;

737

create table t1 (

738

str varchar(255) character set utf8mb4 not null,

739

key str (str(2))

740

) engine=myisam;

741

INSERT INTO t1 VALUES ('str');

742

INSERT INTO t1 VALUES ('str2');

743

select * from t1 where str='str';

744

str

745

str

746

drop table t1;

747

create table t1 (

748

str varchar(255) character set utf8mb4 not null,

749

key str (str(2))

750

) engine=innodb;

751

INSERT INTO t1 VALUES ('str');

752

INSERT INTO t1 VALUES ('str2');

753

select * from t1 where str='str';

754

str

755

str

756

drop table t1;

757

create table t1 (

758

str varchar(255) character set utf8mb4 not null,

759

key str using btree (str(2))

760

) engine=heap;

761

INSERT INTO t1 VALUES ('str');

762

INSERT INTO t1 VALUES ('str2');

763

select * from t1 where str='str';

764

str

765

str

766

drop table t1;

767

create table t1 (

768

str varchar(255) character set utf8mb4 not null,

769

key str using hash (str(2))

770

) engine=heap;

771

INSERT INTO t1 VALUES ('str');

772

INSERT INTO t1 VALUES ('str2');

773

select * from t1 where str='str';

774

str

775

str

776

drop table t1;

777

create table t1 (

778

str varchar(255) character set utf8mb4 not null,

779

key str (str(2))

780

) engine=innodb;

781

INSERT INTO t1 VALUES ('str');

782

INSERT INTO t1 VALUES ('str2');

783

select * from t1 where str='str';

784

str

785

str

786

drop table t1;

787

CREATE TABLE t1 (a varchar(32) BINARY) CHARACTER SET utf8mb4;

788

INSERT INTO t1 VALUES ('test');

789

SELECT a FROM t1 WHERE a LIKE '%te';

790

791

DROP TABLE t1;

792

SET NAMES utf8mb4;

793

CREATE TABLE t1 (

794

subject varchar(255) character set utf8mb4 collate utf8mb4_unicode_ci,

795

p varchar(15) character set utf8mb4

796

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

797

INSERT INTO t1 VALUES ('谷川俊二と申しますが、インターネット予約の会員登録をしましたところ、メールアドレスを間違えてしまい会員ＩＤが受け取ることが出来ませんでした。間違えアドレスはtani-shun@n.vodafone.ne.jpを書き込みました。どうすればよいですか？その他、住所等は間違えありません。連絡ください。よろしくお願いします。m(__)m','040312-000057');

798

INSERT INTO t1 VALUES ('aaa','bbb');

799

SELECT length(subject) FROM t1;

800

length(subject)

801

432

802

803

SELECT length(subject) FROM t1 ORDER BY 1;

804

length(subject)

805

806

432

807

DROP TABLE t1;

808

CREATE TABLE t1 (

809

id int unsigned NOT NULL auto_increment,

810

list_id smallint unsigned NOT NULL,

811

term TEXT NOT NULL,

812

PRIMARY KEY(id),

813

INDEX(list_id, term(4))

814

) ENGINE=MYISAM CHARSET=utf8mb4;

815

INSERT INTO t1 SET list_id = 1, term = "letterc";

816

INSERT INTO t1 SET list_id = 1, term = "letterb";

817

INSERT INTO t1 SET list_id = 1, term = "lettera";

818

INSERT INTO t1 SET list_id = 1, term = "letterd";

819

SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc");

820

821

822

SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb");

823

824

825

SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");

826

827

828

SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");

829

830

831

DROP TABLE t1;

832

SET NAMES latin1;

833

CREATE TABLE t1 (

834

id int unsigned NOT NULL auto_increment,

835

list_id smallint unsigned NOT NULL,

836

term text NOT NULL,

837

PRIMARY KEY(id),

838

INDEX(list_id, term(19))

839

) ENGINE=MyISAM CHARSET=utf8mb4;

840

INSERT INTO t1 set list_id = 1, term = "test�test";

841

INSERT INTO t1 set list_id = 1, term = "testetest";

842

INSERT INTO t1 set list_id = 1, term = "test�test";

843

SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test");

844

id term

845

1 test�test

846

2 testetest

847

3 test�test

848

SELECT id, term FROM t1 where (list_id = 1) AND (term = "testetest");

849

id term

850

1 test�test

851

2 testetest

852

3 test�test

853

SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test");

854

id term

855

1 test�test

856

2 testetest

857

3 test�test

858

DROP TABLE t1;

859

set names utf8mb4;

860

create table t1 (

861

a int primary key,

862

b varchar(6),

863

index b3(b(3))

864

) engine=innodb character set=utf8mb4;

865

insert into t1 values(1,'foo'),(2,'foobar');

866

select * from t1 where b like 'foob%';

867

a b

868

2 foobar

869

alter table t1 engine=innodb;

870

select * from t1 where b like 'foob%';

871

a b

872

2 foobar

873

drop table t1;

874

create table t1 (

875

a enum('петя','вася','анюта') character set utf8mb4 not null default 'анюта',

876

b set('петя','вася','анюта') character set utf8mb4 not null default 'анюта'

877

);

878

create table t2 select concat(a,_utf8mb4'') as a, concat(b,_utf8mb4'')as b from t1;

879

show create table t2;

880

Table Create Table

881

t2 CREATE TABLE `t2` (

882

`a` varchar(5) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',

883

`b` varchar(15) CHARACTER SET utf8mb4 NOT NULL DEFAULT ''

884

) ENGINE=MyISAM DEFAULT CHARSET=latin1

885

drop table t2;

886

drop table t1;

887

select 'c' like '\_' as want0;

888

want0

889

890

SELECT SUBSTR('вася',-2);

891

SUBSTR('вася',-2)

892

ся

893

create table t1 (id integer, a varchar(100) character set utf8mb4 collate utf8mb4_unicode_ci);

894

insert into t1 values (1, 'Test');

895

select * from t1 where soundex(a) = soundex('Test');

896

id a

897

1 Test

898

select * from t1 where soundex(a) = soundex('TEST');

899

id a

900

1 Test

901

select * from t1 where soundex(a) = soundex('test');

902

id a

903

1 Test

904

drop table t1;

905

select soundex(_utf8mb4 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB);

906

soundex(_utf8mb4 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB)

907

阅000

908

select hex(soundex(_utf8mb4 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB));

909

hex(soundex(_utf8mb4 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB))

910

E99885303030

911

select soundex(_utf8mb4 0xD091D092D093);

912

soundex(_utf8mb4 0xD091D092D093)

913

Б000

914

select hex(soundex(_utf8mb4 0xD091D092D093));

915

hex(soundex(_utf8mb4 0xD091D092D093))

916

D091303030

917

SET collation_connection='utf8mb4_general_ci';

918

create table t1 select repeat('a',4000) a;

919

delete from t1;

920

insert into t1 values ('a'), ('a '), ('a\t');

921

select collation(a),hex(a) from t1 order by a;

922

collation(a) hex(a)

923

utf8mb4_general_ci 6109

924

utf8mb4_general_ci 61

925

utf8mb4_general_ci 6120

926

drop table t1;

927

select @@collation_connection;

928

@@collation_connection

929

utf8mb4_general_ci

930

create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;

931

insert into t1 values('abcdef');

932

insert into t1 values('_bcdef');

933

insert into t1 values('a_cdef');

934

insert into t1 values('ab_def');

935

insert into t1 values('abc_ef');

936

insert into t1 values('abcd_f');

937

insert into t1 values('abcde_');

938

select c1 as c1u from t1 where c1 like 'ab\_def';

939

c1u

940

ab_def

941

select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';

942

c2h

943

ab_def

944

drop table t1;

945

drop table if exists t1;

946

create table t1 as select repeat(' ', 64) as s1;

947

select collation(s1) from t1;

948

collation(s1)

949

utf8mb4_general_ci

950

delete from t1;

951

insert into t1 values ('a'),('ae'),(_latin1 0xE4);

952

insert into t1 values ('o'),('oe'),(_latin1 0xF6);

953

insert into t1 values ('s'),('ss'),(_latin1 0xDF);

954

insert into t1 values ('u'),('ue'),(_latin1 0xFC);

955

select s1, hex(s1) from t1 order by s1, binary s1;

956

s1 hex(s1)

957

a 61

958

ä C3A4

959

ae 6165

960

o 6F

961

ö C3B6

962

oe 6F65

963

s 73

964

ß C39F

965

ss 7373

966

u 75

967

ü C3BC

968

ue 7565

969

select group_concat(s1 order by binary s1) from t1 group by s1;

970

group_concat(s1 order by binary s1)

971

a,ä

972

973

o,ö

974

975

s,ß

976

977

u,ü

978

979

drop table t1;

980

SET collation_connection='utf8mb4_bin';

981

create table t1 select repeat('a',4000) a;

982

delete from t1;

983

insert into t1 values ('a'), ('a '), ('a\t');

984

select collation(a),hex(a) from t1 order by a;

985

collation(a) hex(a)

986

utf8mb4_bin 6109

987

utf8mb4_bin 61

988

utf8mb4_bin 6120

989

drop table t1;

990

select @@collation_connection;

991

@@collation_connection

992

utf8mb4_bin

993

create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ;

994

insert into t1 values('abcdef');

995

insert into t1 values('_bcdef');

996

insert into t1 values('a_cdef');

997

insert into t1 values('ab_def');

998

insert into t1 values('abc_ef');

999

insert into t1 values('abcd_f');

1000

insert into t1 values('abcde_');

1001

select c1 as c1u from t1 where c1 like 'ab\_def';

1002

c1u

1003

ab_def

1004

select c1 as c2h from t1 where c1 like 'ab#_def' escape '#';

1005

c2h

1006

ab_def

1007

drop table t1;

1008

CREATE TABLE t1 (

1009

user varchar(255) NOT NULL default ''

1010

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

1011

INSERT INTO t1 VALUES ('one'),('two');

1012

SELECT CHARSET('a');

1013

CHARSET('a')

1014

utf8mb4

1015

SELECT user, CONCAT('<', user, '>') AS c FROM t1;

1016

user c

1017

one <one>

1018

two <two>

1019

DROP TABLE t1;

1020

create table t1 (f1 varchar(1) not null) default charset utf8mb4;

1021

insert into t1 values (''), ('');

1022

select concat(concat(_latin1'->',f1),_latin1'<-') from t1;

1023

concat(concat(_latin1'->',f1),_latin1'<-')

1024

-><-

1025

-><-

1026

drop table t1;

1027

select convert(_koi8r'�' using utf8mb4) < convert(_koi8r'�' using utf8mb4);

1028

convert(_koi8r'?' using utf8mb4) < convert(_koi8r'?' using utf8mb4)

1029

1030

set names latin1;

1031

create table t1 (a varchar(10)) character set utf8mb4;

1032

insert into t1 values ('test');

1033

select ifnull(a,'') from t1;

1034

ifnull(a,'')

1035

test

1036

drop table t1;

1037

select repeat(_utf8mb4'+',3) as h union select NULL;

1038

1039

+++

1040

NULL

1041

select ifnull(NULL, _utf8mb4'string');

1042

ifnull(NULL, _utf8mb4'string')

1043

string

1044

set names utf8mb4;

1045

create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_lithuanian_ci);

1046

insert into t1 values ('I'),('K'),('Y');

1047

select * from t1 where s1 < 'K' and s1 = 'Y';

1048

1049

1050

1051

select * from t1 where 'K' > s1 and s1 = 'Y';

1052

1053

1054

1055

drop table t1;

1056

create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_czech_ci);

1057

insert into t1 values ('c'),('d'),('h'),('ch'),('CH'),('cH'),('Ch'),('i');

1058

select * from t1 where s1 > 'd' and s1 = 'CH';

1059

1060

1061

1062

1063

select * from t1 where 'd' < s1 and s1 = 'CH';

1064

1065

1066

1067

1068

select * from t1 where s1 = 'cH' and s1 <> 'ch';

1069

1070

1071

select * from t1 where 'cH' = s1 and s1 <> 'ch';

1072

1073

1074

drop table t1;

1075

create table t1 (a varchar(255)) default character set utf8mb4;

1076

insert into t1 values (1.0);

1077

drop table t1;

1078

create table t1 (

1079

id int not null,

1080

city varchar(20) not null,

1081

key (city(7),id)

1082

) character set=utf8mb4;

1083

insert into t1 values (1,'Durban North');

1084

insert into t1 values (2,'Durban');

1085

select * from t1 where city = 'Durban';

1086

id city

1087

2 Durban

1088

select * from t1 where city = 'Durban ';

1089

id city

1090

2 Durban

1091

drop table t1;

1092

create table t1 (x set('A', 'B') default 0) character set utf8mb4;

1093

ERROR 42000: Invalid default value for 'x'

1094

create table t1 (x enum('A', 'B') default 0) character set utf8mb4;

1095

ERROR 42000: Invalid default value for 'x'

1096

SET NAMES UTF8;

1097

CREATE TABLE t1 (

1098

`id` int(20) NOT NULL auto_increment,

1099

`country` varchar(100) NOT NULL default '',

1100

`shortcode` varchar(100) NOT NULL default '',

1101

`operator` varchar(100) NOT NULL default '',

1102

`momid` varchar(30) NOT NULL default '',

1103

`keyword` varchar(160) NOT NULL default '',

1104

`content` varchar(160) NOT NULL default '',

1105

`second_token` varchar(160) default NULL,

1106

`gateway_id` int(11) NOT NULL default '0',

1107

`created` datetime NOT NULL default '0000-00-00 00:00:00',

1108

`msisdn` varchar(15) NOT NULL default '',

1109

PRIMARY KEY (`id`),

1110

UNIQUE KEY `MSCCSPK_20030521130957121` (`momid`),

1111

KEY `IX_mobile_originated_message_keyword` (`keyword`),

1112

KEY `IX_mobile_originated_message_created` (`created`),

1113

KEY `IX_mobile_originated_message_support` (`msisdn`,`momid`,`keyword`,`gateway_id`,`created`)

1114

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

1115

INSERT INTO t1 VALUES

1116

(1,'blah','464','aaa','fkc1c9ilc20x0hgae7lx6j09','ERR','ERR Имри.Афимим.Аеимимримдмримрмрирор имримримримр имридм ирбднримрфмририримрфмфмим.Ад.Д имдимримрад.Адимримримрмдиримримримр м.Дадимфшьмримд им.Адимимрн имадми','ИМРИ.АФИМИМ.АЕИМИМРИМДМРИМРМРИРОР',3,'2005-06-01 17:30:43','1234567890'),

1117

(2,'blah','464','aaa','haxpl2ilc20x00bj4tt2m5ti','11','11 g','G',3,'2005-06-02 22:43:10','1234567890');

1118

CREATE TABLE t2 (

1119

`msisdn` varchar(15) NOT NULL default '',

1120

`operator_id` int(11) NOT NULL default '0',

1121

`created` datetime NOT NULL default '0000-00-00 00:00:00',

1122

UNIQUE KEY `PK_user` (`msisdn`)

1123

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1124

INSERT INTO t2 VALUES ('1234567890',2,'2005-05-24 13:53:25');

1125

SELECT content, t2.msisdn FROM t1, t2 WHERE t1.msisdn = '1234567890';

1126

content msisdn

1127

ERR Имри.Афимим.Аеимимримдмримрмрирор имримримримр имридм ирбднримрфмририримрфмфмим.Ад.Д имдимримрад.Адимримримрмдиримримримр м.Дадимфшьмримд им.Адимимрн имадми 1234567890

1128

11 g 1234567890

1129

DROP TABLE t1,t2;

1130

create table t1 (a char(20) character set utf8mb4);

1131

insert into t1 values ('123456'),('андрей');

1132

alter table t1 modify a char(2) character set utf8mb4;

1133

Warnings:

1134

Warning 1265 Data truncated for column 'a' at row 1

1135

Warning 1265 Data truncated for column 'a' at row 2

1136

select char_length(a), length(a), a from t1 order by a;

1137

char_length(a) length(a) a

1138

2 2 12

1139

2 4 ан

1140

drop table t1;

1141

set names utf8mb4;

1142

select 'andre%' like 'andreñ%' escape 'ñ';

1143

'andre%' like 'andreñ%' escape 'ñ'

1144

1145

set names utf8mb4;

1146

select 'a\\' like 'a\\';

1147

'a\\' like 'a\\'

1148

1149

select 'aa\\' like 'a%\\';

1150

'aa\\' like 'a%\\'

1151

1152

create table t1 (a char(10), key(a)) character set utf8mb4;

1153

insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");

1154

select * from t1 where a like "abc%";

1155

1156

abc

1157

abcd

1158

select * from t1 where a like concat("abc","%");

1159

1160

abc

1161

abcd

1162

select * from t1 where a like "ABC%";

1163

1164

abc

1165

abcd

1166

select * from t1 where a like "test%";

1167

1168

test

1169

select * from t1 where a like "te_t";

1170

1171

test

1172

select * from t1 where a like "%a%";

1173

1174

1175

abc

1176

abcd

1177

select * from t1 where a like "%abcd%";

1178

1179

abcd

1180

select * from t1 where a like "%abc\d%";

1181

1182

abcd

1183

drop table t1;

1184

CREATE TABLE t1 (

1185

a varchar(255) NOT NULL default '',

1186

KEY a (a)

1187

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;

1188

insert into t1 values (_utf8mb4 0xe880bd);

1189

insert into t1 values (_utf8mb4 0x5b);

1190

select hex(a) from t1;

1191

hex(a)

1192

1193

E880BD

1194

drop table t1;

1195

set names 'latin1';

1196

create table t1 (a varchar(255)) default charset=utf8mb4;

1197

select * from t1 where find_in_set('-1', a);

1198

1199

drop table t1;

1200

create table t1 (a int);

1201

insert into t1 values (48),(49),(50);

1202

set names utf8mb4;

1203

select distinct char(a) from t1;

1204

char(a)

1205

1206

1207

1208

drop table t1;

1209

CREATE TABLE t1 (t TINYTEXT CHARACTER SET utf8mb4);

1210

INSERT INTO t1 VALUES(REPEAT('a', 100));

1211

CREATE TEMPORARY TABLE t2 SELECT COALESCE(t) AS bug FROM t1;

1212

SELECT LENGTH(bug) FROM t2;

1213

LENGTH(bug)

1214

100

1215

DROP TABLE t2;

1216

DROP TABLE t1;

1217

CREATE TABLE t1 (item varchar(255)) default character set utf8mb4;

1218

INSERT INTO t1 VALUES (N'\\');

1219

INSERT INTO t1 VALUES (_utf8mb4'\\');

1220

INSERT INTO t1 VALUES (N'Cote d\'Ivoire');

1221

INSERT INTO t1 VALUES (_utf8mb4'Cote d\'Ivoire');

1222

SELECT item FROM t1 ORDER BY item;

1223

item

1224

Cote d'Ivoire

1225

Cote d'Ivoire

1226

1227

1228

DROP TABLE t1;

1229

SET NAMES utf8mb4;

1230

DROP TABLE IF EXISTS t1;

1231

Warnings:

1232

Note 1051 Unknown table 't1'

1233

CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

1234

INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');

1235

INSERT INTO t1 VALUES('uu');

1236

check table t1;

1237

Table Op Msg_type Msg_text

1238

test.t1 check status OK

1239

INSERT INTO t1 VALUES('uU');

1240

check table t1;

1241

Table Op Msg_type Msg_text

1242

test.t1 check status OK

1243

INSERT INTO t1 VALUES('uu');

1244

check table t1;

1245

Table Op Msg_type Msg_text

1246

test.t1 check status OK

1247

INSERT INTO t1 VALUES('uuABC');

1248

check table t1;

1249

Table Op Msg_type Msg_text

1250

test.t1 check status OK

1251

INSERT INTO t1 VALUES('UuABC');

1252

check table t1;

1253

Table Op Msg_type Msg_text

1254

test.t1 check status OK

1255

INSERT INTO t1 VALUES('uuABC');

1256

check table t1;

1257

Table Op Msg_type Msg_text

1258

test.t1 check status OK

1259

alter table t1 add b int;

1260

1261

1262

delete from t1 where b=1;

1263

INSERT INTO t1 VALUES('UUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1);

1264

check table t1;

1265

Table Op Msg_type Msg_text

1266

test.t1 check status OK

1267

1268

1269

delete from t1 where b=3;

1270

INSERT INTO t1 VALUES('uUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3);

1271

check table t1;

1272

Table Op Msg_type Msg_text

1273

test.t1 check status OK

1274

drop table t1;

1275

set names utf8mb4;

1276

create table t1 (s1 char(5) character set utf8mb4);

1277

insert into t1 values

1278

('a'),('b'),(null),('ペテルグル'),('ü'),('Y');

1279

create index it1 on t1 (s1);

1280

select s1 as before_delete_general_ci from t1 where s1 like 'ペテ%';

1281

before_delete_general_ci

1282

ペテルグル

1283

delete from t1 where s1 = 'Y';

1284

select s1 as after_delete_general_ci from t1 where s1 like 'ペテ%';

1285

after_delete_general_ci

1286

ペテルグル

1287

drop table t1;

1288

set names utf8mb4;

1289

create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_unicode_ci);

1290

insert into t1 values

1291

('a'),('b'),(null),('ペテルグル'),('ü'),('Y');

1292

create index it1 on t1 (s1);

1293

select s1 as before_delete_unicode_ci from t1 where s1 like 'ペテ%';

1294

before_delete_unicode_ci

1295

ペテルグル

1296

delete from t1 where s1 = 'Y';

1297

select s1 as after_delete_unicode_ci from t1 where s1 like 'ペテ%';

1298

after_delete_unicode_ci

1299

ペテルグル

1300

drop table t1;

1301

set names utf8mb4;

1302

create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_bin);

1303

insert into t1 values

1304

('a'),('b'),(null),('ペテルグル'),('ü'),('Y');

1305

create index it1 on t1 (s1);

1306

select s1 as before_delete_bin from t1 where s1 like 'ペテ%';

1307

before_delete_bin

1308

ペテルグル

1309

delete from t1 where s1 = 'Y';

1310

select s1 as after_delete_bin from t1 where s1 like 'ペテ%';

1311

after_delete_bin

1312

ペテルグル

1313

drop table t1;

1314

set names utf8mb4;

1315

create table t1 (a varchar(30) not null primary key)

1316

engine=innodb default character set utf8mb4 collate utf8mb4_general_ci;

1317

insert into t1 values ('あいうえおかきくけこさしすせそ');

1318

insert into t1 values ('さしすせそかきくけこあいうえお');

1319

select a as gci1 from t1 where a like 'さしすせそかきくけこあいうえお%';

1320

gci1

1321

さしすせそかきくけこあいうえお

1322

select a as gci2 from t1 where a like 'あいうえおかきくけこさしすせそ';

1323

gci2

1324

あいうえおかきくけこさしすせそ

1325

drop table t1;

1326

set names utf8mb4;

1327

create table t1 (a varchar(30) not null primary key)

1328

engine=innodb default character set utf8mb4 collate utf8mb4_unicode_ci;

1329

insert into t1 values ('あいうえおかきくけこさしすせそ');

1330

insert into t1 values ('さしすせそかきくけこあいうえお');

1331

select a as uci1 from t1 where a like 'さしすせそかきくけこあいうえお%';

1332

uci1

1333

さしすせそかきくけこあいうえお

1334

select a as uci2 from t1 where a like 'あいうえおかきくけこさしすせそ';

1335

uci2

1336

あいうえおかきくけこさしすせそ

1337

drop table t1;

1338

set names utf8mb4;

1339

create table t1 (a varchar(30) not null primary key)

1340

engine=innodb default character set utf8mb4 collate utf8mb4_bin;

1341

insert into t1 values ('あいうえおかきくけこさしすせそ');

1342

insert into t1 values ('さしすせそかきくけこあいうえお');

1343

select a as bin1 from t1 where a like 'さしすせそかきくけこあいうえお%';

1344

bin1

1345

さしすせそかきくけこあいうえお

1346

select a as bin2 from t1 where a like 'あいうえおかきくけこさしすせそ';

1347

bin2

1348

あいうえおかきくけこさしすせそ

1349

drop table t1;

1350

SET NAMES utf8mb4;

1351

CREATE TABLE t1 (id int PRIMARY KEY,

1352

a varchar(16) collate utf8mb4_unicode_ci NOT NULL default '',

1353

b int,

1354

f varchar(128) default 'XXX',

1355

INDEX (a(4))

1356

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

1357

INSERT INTO t1(id, a, b) VALUES

1358

(1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30),

1359

(4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40),

1360

(7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50),

1361

(10, 'eeeee', 40), (11, 'bbbbbb', 60);

1362

SELECT id, a, b FROM t1;

1363

id a b

1364

1 cccc 50

1365

2 cccc 70

1366

3 cccc 30

1367

4 cccc 30

1368

5 cccc 20

1369

6 bbbbbb 40

1370

7 dddd 30

1371

8 aaaa 10

1372

9 aaaa 50

1373

10 eeeee 40

1374

11 bbbbbb 60

1375

SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';

1376

id a b

1377

8 aaaa 10

1378

9 aaaa 50

1379

6 bbbbbb 40

1380

11 bbbbbb 60

1381

SELECT id, a FROM t1 WHERE a='bbbbbb';

1382

id a

1383

6 bbbbbb

1384

11 bbbbbb

1385

SELECT id, a FROM t1 WHERE a='bbbbbb' ORDER BY b;

1386

id a

1387

6 bbbbbb

1388

11 bbbbbb

1389

DROP TABLE t1;

1390

SET NAMES utf8mb4;

1391

CREATE TABLE t1 (

1392

a CHAR(13) DEFAULT '',

1393

INDEX(a)

1394

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

1395

INSERT INTO t1 VALUES

1396

('Käli Käli 2-4'), ('Käli Käli 2-4'),

1397

('Käli Käli 2+4'), ('Käli Käli 2+4'),

1398

('Käli Käli 2-6'), ('Käli Käli 2-6');

1399

INSERT INTO t1 SELECT * FROM t1;

1400

CREATE TABLE t2 (

1401

a CHAR(13) DEFAULT '',

1402

INDEX(a)

1403

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

1404

INSERT INTO t2 VALUES

1405

('Kali Kali 2-4'), ('Kali Kali 2-4'),

1406

('Kali Kali 2+4'), ('Kali Kali 2+4'),

1407

('Kali Kali 2-6'), ('Kali Kali 2-6');

1408

INSERT INTO t2 SELECT * FROM t2;

1409

SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4';

1410

1411

Käli Käli 2+4

1412

Käli Käli 2+4

1413

Käli Käli 2+4

1414

Käli Käli 2+4

1415

SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4';

1416

1417

Kali Kali 2+4

1418

Kali Kali 2+4

1419

Kali Kali 2+4

1420

Kali Kali 2+4

1421

EXPLAIN SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4';

1422

id select_type table type possible_keys key key_len ref rows Extra

1423

1 SIMPLE t1 range a a 53 NULL 4 Using where; Using index

1424

EXPLAIN SELECT a FROM t1 WHERE a = 'Käli Käli 2+4';

1425

id select_type table type possible_keys key key_len ref rows Extra

1426

1 SIMPLE t1 ref a a 53 const 4 Using where; Using index

1427

EXPLAIN SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4';

1428

id select_type table type possible_keys key key_len ref rows Extra

1429

1 SIMPLE t2 range a a 14 NULL 4 Using where; Using index

1430

EXPLAIN SELECT a FROM t2 WHERE a = 'Kali Kali 2+4';

1431

id select_type table type possible_keys key key_len ref rows Extra

1432

1 SIMPLE t2 ref a a 14 const 4 Using where; Using index

1433

DROP TABLE t1,t2;

1434

CREATE TABLE t1 (

1435

a char(255) DEFAULT '',

1436

KEY(a(10))

1437

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

1438

INSERT INTO t1 VALUES ('Käli Käli 2-4');

1439

SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';

1440

1441

Käli Käli 2-4

1442

INSERT INTO t1 VALUES ('Käli Käli 2-4');

1443

SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';

1444

1445

Käli Käli 2-4

1446

Käli Käli 2-4

1447

DROP TABLE t1;

1448

CREATE TABLE t1 (

1449

a char(255) DEFAULT ''

1450

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

1451

INSERT INTO t1 VALUES ('Käli Käli 2-4');

1452

INSERT INTO t1 VALUES ('Käli Käli 2-4');

1453

SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';

1454

1455

Käli Käli 2-4

1456

Käli Käli 2-4

1457

ALTER TABLE t1 ADD KEY (a(10));

1458

SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';

1459

1460

Käli Käli 2-4

1461

Käli Käli 2-4

1462

DROP TABLE t1;

1463

SET NAMES latin2;

1464

CREATE TABLE t1 (

1465

id int(11) NOT NULL default '0',

1466

tid int(11) NOT NULL default '0',

1467

val text NOT NULL,

1468

INDEX idx(tid, val(10))

1469

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

1470

INSERT INTO t1 VALUES

1471

(40988,72,'VOLN� ADSL'),(41009,72,'VOLN� ADSL'),

1472

(41032,72,'VOLN� ADSL'),(41038,72,'VOLN� ADSL'),

1473

(41063,72,'VOLN� ADSL'),(41537,72,'VOLN� ADSL Office'),

1474

(42141,72,'VOLN� ADSL'),(42565,72,'VOLN� ADSL Combi'),

1475

(42749,72,'VOLN� ADSL'),(44205,72,'VOLN� ADSL');

1476

SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNY ADSL';

1477

id tid val

1478

40988 72 VOLN� ADSL

1479

41009 72 VOLN� ADSL

1480

41032 72 VOLN� ADSL

1481

41038 72 VOLN� ADSL

1482

41063 72 VOLN� ADSL

1483

42141 72 VOLN� ADSL

1484

42749 72 VOLN� ADSL

1485

44205 72 VOLN� ADSL

1486

SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL';

1487

id tid val

1488

40988 72 VOLN� ADSL

1489

41009 72 VOLN� ADSL

1490

41032 72 VOLN� ADSL

1491

41038 72 VOLN� ADSL

1492

41063 72 VOLN� ADSL

1493

42141 72 VOLN� ADSL

1494

42749 72 VOLN� ADSL

1495

44205 72 VOLN� ADSL

1496

SELECT * FROM t1 WHERE tid=72 and val LIKE '%VOLN� ADSL';

1497

id tid val

1498

40988 72 VOLN� ADSL

1499

41009 72 VOLN� ADSL

1500

41032 72 VOLN� ADSL

1501

41038 72 VOLN� ADSL

1502

41063 72 VOLN� ADSL

1503

42141 72 VOLN� ADSL

1504

42749 72 VOLN� ADSL

1505

44205 72 VOLN� ADSL

1506

ALTER TABLE t1 DROP KEY idx;

1507

ALTER TABLE t1 ADD KEY idx (tid,val(11));

1508

SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL';

1509

id tid val

1510

40988 72 VOLN� ADSL

1511

41009 72 VOLN� ADSL

1512

41032 72 VOLN� ADSL

1513

41038 72 VOLN� ADSL

1514

41063 72 VOLN� ADSL

1515

42141 72 VOLN� ADSL

1516

42749 72 VOLN� ADSL

1517

44205 72 VOLN� ADSL

1518

DROP TABLE t1;

1519

create table t1(a char(200) collate utf8mb4_unicode_ci NOT NULL default '')

1520

default charset=utf8mb4 collate=utf8mb4_unicode_ci;

1521

insert into t1 values (unhex('65')), (unhex('C3A9')), (unhex('65'));

1522

explain select distinct a from t1;

1523

id select_type table type possible_keys key key_len ref rows Extra

1524

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary

1525

select distinct a from t1;

1526

1527

1528

explain select a from t1 group by a;

1529

id select_type table type possible_keys key key_len ref rows Extra

1530

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort

1531

select a from t1 group by a;

1532

1533

1534

drop table t1;

1535

create table t1(a char(10)) default charset utf8mb4;

1536

insert into t1 values ('123'), ('456');

1537

explain

1538

select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;

1539

id select_type table type possible_keys key key_len ref rows Extra

1540

1 SIMPLE Y ALL NULL NULL NULL NULL 2 Using temporary; Using filesort

1541

1 SIMPLE Z ALL NULL NULL NULL NULL 2 Using where; Using join buffer

1542

select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;

1543

substr(Z.a,-1) a

1544

3 123

1545

6 456

1546

drop table t1;

1547

SET CHARACTER SET utf8mb4;

1548

SHOW VARIABLES LIKE 'character\_set\_%';

1549

Variable_name Value

1550

character_set_client utf8mb4

1551

character_set_connection latin1

1552

character_set_database latin1

1553

character_set_filesystem binary

1554

character_set_results utf8mb4

1555

character_set_server latin1

1556

character_set_system utf8

1557

CREATE DATABASE crashtest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

1558

USE crashtest;

1559

CREATE TABLE crashtest (crash char(10)) DEFAULT CHARSET=utf8mb4;

1560

INSERT INTO crashtest VALUES ('35'), ('36'), ('37');

1561

SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8mb4);

1562

crash

1563

1564

1565

1566

INSERT INTO crashtest VALUES ('-1000');

1567

EXPLAIN SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8mb4);

1568

id select_type table type possible_keys key key_len ref rows Extra

1569

1 SIMPLE crashtest ALL NULL NULL NULL NULL 4 Using filesort

1570

SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8mb4);

1571

crash

1572

-1000

1573

1574

1575

1576

Warnings:

1577

Warning 1300 Invalid utf8mb4 character string: 'FFFFFC'

1578

DROP TABLE crashtest;

1579

DROP DATABASE crashtest;

1580

USE test;

1581

SET CHARACTER SET default;

1582

CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8mb4;

1583

INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa');

1584

SELECT id FROM t1;

1585

1586

xxx

1587

1588

yyy

1589

1590

SELECT DISTINCT id FROM t1;

1591

1592

xxx

1593

1594

yyy

1595

SELECT DISTINCT id FROM t1 ORDER BY id;

1596

1597

1598

xxx

1599

yyy

1600

DROP TABLE t1;

1601

create table t1 (

1602

a varchar(26) not null

1603

) default character set utf8mb4;

1604

insert into t1 (a) values ('abcdefghijklmnopqrstuvwxyz');

1605

select * from t1;

1606

1607

abcdefghijklmnopqrstuvwxyz

1608

alter table t1 change a a varchar(20) character set utf8mb4 not null;

1609

Warnings:

1610

Warning 1265 Data truncated for column 'a' at row 1

1611

select * from t1;

1612

1613

abcdefghijklmnopqrst

1614

alter table t1 change a a char(15) character set utf8mb4 not null;

1615

Warnings:

1616

Warning 1265 Data truncated for column 'a' at row 1

1617

select * from t1;

1618

1619

abcdefghijklmno

1620

alter table t1 change a a char(10) character set utf8mb4 not null;

1621

Warnings:

1622

Warning 1265 Data truncated for column 'a' at row 1

1623

select * from t1;

1624

1625

abcdefghij

1626

alter table t1 change a a varchar(5) character set utf8mb4 not null;

1627

Warnings:

1628

Warning 1265 Data truncated for column 'a' at row 1

1629

select * from t1;

1630

1631

abcde

1632

drop table t1;

1633

create table t1 (

1634

a varchar(4000) not null

1635

) default character set utf8mb4;

1636

insert into t1 values (repeat('a',4000));

1637

alter table t1 change a a varchar(3000) character set utf8mb4 not null;

1638

Warnings:

1639

Warning 1265 Data truncated for column 'a' at row 1

1640

select length(a) from t1;

1641

length(a)

1642

3000

1643

drop table t1;

1644

set names utf8mb4;

1645

select hex(char(1 using utf8mb4));

1646

hex(char(1 using utf8mb4))

1647

1648

select char(0xd1,0x8f using utf8mb4);

1649

char(0xd1,0x8f using utf8mb4)

1650

1651

select char(0xd18f using utf8mb4);

1652

char(0xd18f using utf8mb4)

1653

1654

select char(53647 using utf8mb4);

1655

char(53647 using utf8mb4)

1656

1657

select char(0xff,0x8f using utf8mb4);

1658

char(0xff,0x8f using utf8mb4)

1659

1660

Warnings:

1661

Warning 1300 Invalid utf8mb4 character string: 'FF8F'

1662

select convert(char(0xff,0x8f) using utf8mb4);

1663

convert(char(0xff,0x8f) using utf8mb4)

1664

1665

Warnings:

1666

Warning 1300 Invalid utf8mb4 character string: 'FF8F'

1667

set sql_mode=traditional;

1668

select char(0xff,0x8f using utf8mb4);

1669

char(0xff,0x8f using utf8mb4)

1670

NULL

1671

Warnings:

1672

Warning 1300 Invalid utf8mb4 character string: 'FF8F'

1673

select char(195 using utf8mb4);

1674

char(195 using utf8mb4)

1675

NULL

1676

Warnings:

1677

Warning 1300 Invalid utf8mb4 character string: 'C3'

1678

select char(196 using utf8mb4);

1679

char(196 using utf8mb4)

1680

NULL

1681

Warnings:

1682

Warning 1300 Invalid utf8mb4 character string: 'C4'

1683

select char(2557 using utf8mb4);

1684

char(2557 using utf8mb4)

1685

NULL

1686

Warnings:

1687

Warning 1300 Invalid utf8mb4 character string: 'FD'

1688

select convert(char(0xff,0x8f) using utf8mb4);

1689

convert(char(0xff,0x8f) using utf8mb4)

1690

NULL

1691

Warnings:

1692

Warning 1300 Invalid utf8mb4 character string: 'FF8F'

1693

select hex(convert(char(2557 using latin1) using utf8mb4));

1694

hex(convert(char(2557 using latin1) using utf8mb4))

1695

09C3BD

1696

select hex(char(195));

1697

hex(char(195))

1698

1699

select hex(char(196));

1700

hex(char(196))

1701

1702

select hex(char(2557));

1703

hex(char(2557))

1704

09FD

1705

set names utf8mb4;

1706

create table t1 (a char(1)) default character set utf8mb4;

1707

create table t2 (a char(1)) default character set utf8mb4;

1708

insert into t1 values('a'),('a'),(0xE38182),(0xE38182);

1709

insert into t1 values('i'),('i'),(0xE38184),(0xE38184);

1710

select * from t1 union distinct select * from t2;

1711

1712

1713

あ

1714

1715

い

1716

drop table t1,t2;

1717

set names utf8mb4;

1718

create table t1 (a char(10), b varchar(10));

1719

insert into t1 values ('bar','kostja');

1720

insert into t1 values ('kostja','bar');

1721

prepare my_stmt from "select * from t1 where a=?";

1722

set @a:='bar';

1723

execute my_stmt using @a;

1724

a b

1725

bar kostja

1726

set @a:='kostja';

1727

execute my_stmt using @a;

1728

a b

1729

kostja bar

1730

set @a:=null;

1731

execute my_stmt using @a;

1732

a b

1733

drop table if exists t1;

1734

drop table if exists t1;

1735

drop view if exists v1, v2;

1736

set names utf8mb4;

1737

create table t1(col1 varchar(12) character set utf8mb4 collate utf8mb4_unicode_ci);

1738

insert into t1 values('t1_val');

1739

create view v1 as select 'v1_val' as col1;

1740

select coercibility(col1), collation(col1) from v1;

1741

coercibility(col1) collation(col1)

1742

4 utf8mb4_general_ci

1743

create view v2 as select col1 from v1 union select col1 from t1;

1744

select coercibility(col1), collation(col1)from v2;

1745

coercibility(col1) collation(col1)

1746

2 utf8mb4_unicode_ci

1747

2 utf8mb4_unicode_ci

1748

drop view v1, v2;

1749

create view v1 as select 'v1_val' collate utf8mb4_swedish_ci as col1;

1750

select coercibility(col1), collation(col1) from v1;

1751

coercibility(col1) collation(col1)

1752

0 utf8mb4_swedish_ci

1753

create view v2 as select col1 from v1 union select col1 from t1;

1754

select coercibility(col1), collation(col1) from v2;

1755

coercibility(col1) collation(col1)

1756

0 utf8mb4_swedish_ci

1757

0 utf8mb4_swedish_ci

1758

drop view v1, v2;

1759

drop table t1;

1760

set names utf8mb4;

1761

create table t1 (a varchar(10) character set latin1, b int);

1762

insert into t1 values ('a',1);

1763

select concat(a, if(b>10, N'x', N'y')) from t1;

1764

concat(a, if(b>10, N'x', N'y'))

1765

1766

select concat(a, if(b>10, N'æ', N'ß')) from t1;

1767

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'

1768

drop table t1;

1769

set names utf8mb4;

1770

create table t1 (a varchar(10) character set latin1, b int);

1771

insert into t1 values ('a',1);

1772

select concat(a, if(b>10, _utf8mb4'x', _utf8mb4'y')) from t1;

1773

concat(a, if(b>10, _utf8mb4'x', _utf8mb4'y'))

1774

1775

select concat(a, if(b>10, _utf8mb4'æ', _utf8mb4'ß')) from t1;

1776

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat'

1777

drop table t1;

1778

set names utf8mb4;

1779

create table t1 (a varchar(10) character set latin1, b int);

1780

insert into t1 values ('a',1);

1781

select concat(a, if(b>10, _utf8mb4 0x78, _utf8mb4 0x79)) from t1;

1782

concat(a, if(b>10, _utf8mb4 0x78, _utf8mb4 0x79))

1783

1784

select concat(a, if(b>10, _utf8mb4 0xC3A6, _utf8mb4 0xC3AF)) from t1;

1785

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat'

1786

drop table t1;

1787

set names utf8mb4;

1788

create table t1 (a varchar(10) character set latin1, b int);

1789

insert into t1 values ('a',1);

1790

select concat(a, if(b>10, 'x' 'x', 'y' 'y')) from t1;

1791

concat(a, if(b>10, 'x' 'x', 'y' 'y'))

1792

ayy

1793

select concat(a, if(b>10, 'x' 'æ', 'y' 'ß')) from t1;

1794

ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat'

1795

drop table t1;

1796

CREATE TABLE t1 (

1797

colA int(11) NOT NULL,

1798

colB varchar(255) character set utf8mb4 NOT NULL,

1799

PRIMARY KEY (colA)

1800

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1801

INSERT INTO t1 (colA, colB) VALUES (1, 'foo'), (2, 'foo bar');

1802

CREATE TABLE t2 (

1803

colA int(11) NOT NULL,

1804

colB varchar(255) character set utf8mb4 NOT NULL,

1805

KEY bad (colA,colB(3))

1806

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1807

INSERT INTO t2 (colA, colB) VALUES (1, 'foo'),(2, 'foo bar');

1808

SELECT * FROM t1 JOIN t2 ON t1.colA=t2.colA AND t1.colB=t2.colB

1809

WHERE t1.colA < 3;

1810

colA colB colA colB

1811

1 foo 1 foo

1812

2 foo bar 2 foo bar

1813

DROP TABLE t1, t2;

1814

SELECT 'н1234567890' UNION SELECT _binary '1';

1815

н1234567890

1816

н1234567890

1817

1818

SELECT 'н1234567890' UNION SELECT 1;

1819

н1234567890

1820

н1234567890

1821

1822

SELECT '1' UNION SELECT 'н1234567890';

1823

1824

1825

н1234567890

1826

SELECT 1 UNION SELECT 'н1234567890';

1827

1828

1829

н1234567890

1830

CREATE TABLE t1 (c VARCHAR(11)) CHARACTER SET utf8mb4;

1831

CREATE TABLE t2 (b CHAR(1) CHARACTER SET binary, i INT);

1832

INSERT INTO t1 (c) VALUES ('н1234567890');

1833

INSERT INTO t2 (b, i) VALUES ('1', 1);

1834

SELECT c FROM t1 UNION SELECT b FROM t2;

1835

1836

н1234567890

1837

1838

SELECT c FROM t1 UNION SELECT i FROM t2;

1839

1840

н1234567890

1841

1842

SELECT b FROM t2 UNION SELECT c FROM t1;

1843

1844

1845

н1234567890

1846

SELECT i FROM t2 UNION SELECT c FROM t1;

1847

1848

1849

н1234567890

1850

DROP TABLE t1, t2;

1851

set sql_mode=traditional;

1852

select hex(char(0xFF using utf8mb4));

1853

hex(char(0xFF using utf8mb4))

1854

NULL

1855

Warnings:

1856

Warning 1300 Invalid utf8mb4 character string: 'FF'

1857

select hex(convert(0xFF using utf8mb4));

1858

hex(convert(0xFF using utf8mb4))

1859

NULL

1860

Warnings:

1861

Warning 1300 Invalid utf8mb4 character string: 'FF'

1862

select hex(_utf8mb4 0x616263FF);

1863

ERROR HY000: Invalid utf8mb4 character string: 'FF'

1864

select hex(_utf8mb4 X'616263FF');

1865

ERROR HY000: Invalid utf8mb4 character string: 'FF'

1866

select hex(_utf8mb4 B'001111111111');

1867

ERROR HY000: Invalid utf8mb4 character string: 'FF'

1868

select (_utf8mb4 X'616263FF');

1869

ERROR HY000: Invalid utf8mb4 character string: 'FF'

1870

set sql_mode=default;

1871

select hex(char(0xFF using utf8mb4));

1872

hex(char(0xFF using utf8mb4))

1873

1874

Warnings:

1875

Warning 1300 Invalid utf8mb4 character string: 'FF'

1876

select hex(convert(0xFF using utf8mb4));

1877

hex(convert(0xFF using utf8mb4))

1878

1879

Warnings:

1880

Warning 1300 Invalid utf8mb4 character string: 'FF'

1881

select hex(_utf8mb4 0x616263FF);

1882

ERROR HY000: Invalid utf8mb4 character string: 'FF'

1883

select hex(_utf8mb4 X'616263FF');

1884

ERROR HY000: Invalid utf8mb4 character string: 'FF'

1885

select hex(_utf8mb4 B'001111111111');

1886

ERROR HY000: Invalid utf8mb4 character string: 'FF'

1887

select (_utf8mb4 X'616263FF');

1888

ERROR HY000: Invalid utf8mb4 character string: 'FF'

1889

CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);

1890

INSERT INTO t1 VALUES (70000, 1092), (70001, 1085), (70002, 1065);

1891

SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;

1892

CONVERT(a, CHAR) CONVERT(b, CHAR)

1893

70002 1065

1894

70001 1085

1895

70000 1092

1896

SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1;

1897

CONVERT(a, CHAR) CONVERT(b, CHAR)

1898

70000 1092

1899

70001 1085

1900

70002 1065

1901

ALTER TABLE t1 ADD UNIQUE (b);

1902

SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;

1903

CONVERT(a, CHAR) CONVERT(b, CHAR)

1904

70002 1065

1905

70001 1085

1906

70000 1092

1907

DROP INDEX b ON t1;

1908

SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;

1909

CONVERT(a, CHAR) CONVERT(b, CHAR)

1910

70002 1065

1911

70001 1085

1912

70000 1092

1913

ALTER TABLE t1 ADD INDEX (b);

1914

SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) from t1 GROUP BY b;

1915

CONVERT(a, CHAR) CONVERT(b, CHAR)

1916

70002 1065

1917

70001 1085

1918

70000 1092

1919

DROP TABLE t1;

1920

1921

# Bug#26474: Add Sinhala script (Sri Lanka) collation to MySQL

1922

1923

DROP TABLE IF EXISTS t1;

1924

CREATE TABLE t1 (

1925

predicted_order int NOT NULL,

1926

utf8mb4_encoding VARCHAR(10) NOT NULL

1927

) CHARACTER SET utf8mb4;

1928

INSERT INTO t1 VALUES (19, x'E0B696'), (30, x'E0B69AE0B798'), (61, x'E0B6AF'), (93, x'E0B799'), (52, x'E0B6A6'), (73, x'E0B6BBE0B78AE2808D'), (3, x'E0B686'), (56, x'E0B6AA'), (55, x'E0B6A9'), (70, x'E0B6B9'), (94, x'E0B79A'), (80, x'E0B785'), (25, x'E0B69AE0B791'), (48, x'E0B6A2'), (13, x'E0B690'), (86, x'E0B793'), (91, x'E0B79F'), (81, x'E0B786'), (79, x'E0B784'), (14, x'E0B691'), (99, x'E0B78A'), (8, x'E0B68B'), (68, x'E0B6B7'), (22, x'E0B69A'), (16, x'E0B693'), (33, x'E0B69AE0B7B3'), (38, x'E0B69AE0B79D'), (21, x'E0B683'), (11, x'E0B68E'), (77, x'E0B782'), (40, x'E0B69AE0B78A'), (101, x'E0B78AE2808DE0B6BB'), (35, x'E0B69AE0B79A'), (1, x'E0B7B4'), (9, x'E0B68C'), (96, x'E0B79C'), (6, x'E0B689'), (95, x'E0B79B'), (88, x'E0B796'), (64, x'E0B6B3'), (26, x'E0B69AE0B792'), (82, x'E0B78F'), (28, x'E0B69AE0B794'), (39, x'E0B69AE0B79E'), (97, x'E0B79D'), (2, x'E0B685'), (75, x'E0B780'), (34, x'E0B69AE0B799'), (69, x'E0B6B8'), (83, x'E0B790'), (18, x'E0B695'), (90, x'E0B7B2'), (17, x'E0B694'), (72, x'E0B6BB'), (66, x'E0B6B5'), (59, x'E0B6AD'), (44, x'E0B69E'), (15, x'E0B692'), (23, x'E0B69AE0B78F'), (65, x'E0B6B4'), (42, x'E0B69C'), (63, x'E0B6B1'), (85, x'E0B792'), (47, x'E0B6A1'), (49, x'E0B6A3'), (92, x'E0B7B3'), (78, x'E0B783'), (36, x'E0B69AE0B79B'), (4, x'E0B687'), (24, x'E0B69AE0B790'), (87, x'E0B794'), (37, x'E0B69AE0B79C'), (32, x'E0B69AE0B79F'), (29, x'E0B69AE0B796'), (43, x'E0B69D'), (62, x'E0B6B0'), (100, x'E0B78AE2808DE0B6BA'), (60, x'E0B6AE'), (45, x'E0B69F'), (12, x'E0B68F'), (46, x'E0B6A0'), (50, x'E0B6A5'), (51, x'E0B6A4'), (5, x'E0B688'), (76, x'E0B781'), (89, x'E0B798'), (74, x'E0B6BD'), (10, x'E0B68D'), (57, x'E0B6AB'), (71, x'E0B6BA'), (58, x'E0B6AC'), (27, x'E0B69AE0B793'), (54, x'E0B6A8'), (84, x'E0B791'), (31, x'E0B69AE0B7B2'), (98, x'E0B79E'), (53, x'E0B6A7'), (41, x'E0B69B'), (67, x'E0B6B6'), (7, x'E0B68A'), (20, x'E0B682');

1929

SELECT predicted_order, hex(utf8mb4_encoding) FROM t1 ORDER BY utf8mb4_encoding COLLATE utf8mb4_sinhala_ci;

1930

predicted_order hex(utf8mb4_encoding)

1931

1 E0B7B4

1932

2 E0B685

1933

3 E0B686

1934

4 E0B687

1935

5 E0B688

1936

6 E0B689

1937

7 E0B68A

1938

8 E0B68B

1939

9 E0B68C

1940

10 E0B68D

1941

11 E0B68E

1942

12 E0B68F

1943

13 E0B690

1944

14 E0B691

1945

15 E0B692

1946

16 E0B693

1947

17 E0B694

1948

18 E0B695

1949

19 E0B696

1950

20 E0B682

1951

21 E0B683

1952

22 E0B69A

1953

23 E0B69AE0B78F

1954

24 E0B69AE0B790

1955

25 E0B69AE0B791

1956

26 E0B69AE0B792

1957

27 E0B69AE0B793

1958

28 E0B69AE0B794

1959

29 E0B69AE0B796

1960

30 E0B69AE0B798

1961

31 E0B69AE0B7B2

1962

32 E0B69AE0B79F

1963

33 E0B69AE0B7B3

1964

34 E0B69AE0B799

1965

35 E0B69AE0B79A

1966

36 E0B69AE0B79B

1967

37 E0B69AE0B79C

1968

38 E0B69AE0B79D

1969

39 E0B69AE0B79E

1970

40 E0B69AE0B78A

1971

41 E0B69B

1972

42 E0B69C

1973

43 E0B69D

1974

44 E0B69E

1975

45 E0B69F

1976

46 E0B6A0

1977

47 E0B6A1

1978

48 E0B6A2

1979

49 E0B6A3

1980

50 E0B6A5

1981

51 E0B6A4

1982

52 E0B6A6

1983

53 E0B6A7

1984

54 E0B6A8

1985

55 E0B6A9

1986

56 E0B6AA

1987

57 E0B6AB

1988

58 E0B6AC

1989

59 E0B6AD

1990

60 E0B6AE

1991

61 E0B6AF

1992

62 E0B6B0

1993

63 E0B6B1

1994

64 E0B6B3

1995

65 E0B6B4

1996

66 E0B6B5

1997

67 E0B6B6

1998

68 E0B6B7

1999

69 E0B6B8

2000

70 E0B6B9

2001

71 E0B6BA

2002

72 E0B6BB

2003

73 E0B6BBE0B78AE2808D

2004

74 E0B6BD

2005

75 E0B780

2006

76 E0B781

2007

77 E0B782

2008

78 E0B783

2009

79 E0B784

2010

80 E0B785

2011

81 E0B786

2012

82 E0B78F

2013

83 E0B790

2014

84 E0B791

2015

85 E0B792

2016

86 E0B793

2017

87 E0B794

2018

88 E0B796

2019

89 E0B798

2020

90 E0B7B2

2021

91 E0B79F

2022

92 E0B7B3

2023

93 E0B799

2024

94 E0B79A

2025

95 E0B79B

2026

96 E0B79C

2027

97 E0B79D

2028

98 E0B79E

2029

99 E0B78A

2030

100 E0B78AE2808DE0B6BA

2031

101 E0B78AE2808DE0B6BB

2032

DROP TABLE t1;

2033

2034

# Bug#32914 Character sets: illegal characters in utf8mb4 and utf32 columns

2035

2036

create table t1 (utf8mb4 char(1) character set utf8mb4);

2037

Testing [F0][90..BF][80..BF][80..BF]

2038

insert into t1 values (0xF0908080);

2039

insert into t1 values (0xF0BFBFBF);

2040

insert into t1 values (0xF08F8080);

2041

Warnings:

2042

Warning 1366 Incorrect string value: '\xF0\x8F\x80\x80' for column 'utf8mb4' at row 1

2043

select hex(utf8mb4) from t1;

2044

hex(utf8mb4)

2045

F0908080

2046

F0BFBFBF

2047

2048

delete from t1;

2049

Testing [F2..F3][80..BF][80..BF][80..BF]

2050

insert into t1 values (0xF2808080);

2051

insert into t1 values (0xF2BFBFBF);

2052

select hex(utf8mb4) from t1;

2053

hex(utf8mb4)

2054

F2808080

2055

F2BFBFBF

2056

delete from t1;

2057

Testing [F4][80..8F][80..BF][80..BF]

2058

insert into t1 values (0xF4808080);

2059

insert into t1 values (0xF48F8080);

2060

insert into t1 values (0xF4908080);

2061

Warnings:

2062

Warning 1366 Incorrect string value: '\xF4\x90\x80\x80' for column 'utf8mb4' at row 1

2063

select hex(utf8mb4) from t1;

2064

hex(utf8mb4)

2065

F4808080

2066

F48F8080

2067

2068

drop table t1;

2069

2070

# Check strnxfrm() with odd length

2071

2072

set max_sort_length=5;

2073

select @@max_sort_length;

2074

@@max_sort_length

2075

2076

create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci);

2077

insert into t1 values ('a'),('b'),('c');

2078

select * from t1 order by a;

2079

2080

2081

2082

2083

alter table t1 modify a varchar(128) character set utf8mb4 collate utf8mb4_bin;

2084

select * from t1 order by a;

2085

2086

2087

2088

2089

drop table t1;

2090

set max_sort_length=default;

2091

2092

# Bug#26180: Can't add columns to tables created with utf8mb4 text indexes

2093

2094

CREATE TABLE t1 (

2095

clipid INT NOT NULL,

2096

Tape TINYTEXT,

2097

PRIMARY KEY (clipid),

2098

KEY tape(Tape(255))

2099

) CHARACTER SET=utf8mb4;

2100

ALTER TABLE t1 ADD mos TINYINT DEFAULT 0 AFTER clipid;

2101

SHOW CREATE TABLE t1;

2102

Table Create Table

2103

t1 CREATE TABLE `t1` (

2104

`clipid` int(11) NOT NULL,

2105

`mos` tinyint(4) DEFAULT '0',

2106

`Tape` tinytext,

2107

PRIMARY KEY (`clipid`),

2108

KEY `tape` (`Tape`(255))

2109

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

2110

DROP TABLE t1;

2111

2112

# Testing 4-byte values.

2113

2114

DROP TABLE IF EXISTS t1;

2115

CREATE TABLE t1 (

2116

u_decimal int NOT NULL,

2117

utf8mb4_encoding VARCHAR(10) NOT NULL

2118

) CHARACTER SET utf8mb4;

2119

INSERT INTO t1 VALUES (119040, x'f09d8480'),

2120

# G CLEF

2121

(119070, x'f09d849e'),

2122

# HALF NOTE

2123

(119134, x'f09d859e'),

2124

# MUSICAL SYMBOL CROIX

2125

(119247, x'f09d878f'),

2126

# MATHEMATICAL BOLD ITALIC CAPITAL DELTA

2127

(120607, x'f09d9c9f'),

2128

# SANS-SERIF BOLD ITALIC CAPITAL PI

2129

(120735, x'f09d9e9f'),

2130

# <Plane 16 Private Use, Last> (last 4 byte character)

2131

(1114111, x'f48fbfbf'),

2132

# VARIATION SELECTOR-256

2133

(917999, x'f3a087af');

2134

INSERT INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859ff09d859ff09d85a0f09d85a0f09d8480');

2135

INSERT INTO t1 VALUES (65131, x'efb9abf09d849ef09d859ef09d859ef09d8480f09d859fefb9abefb9abf09d85a0efb9ab');

2136

INSERT INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859ff09d859ff09d85a0f09d85a0f09d8480f09d85a0');

2137

Warnings:

2138

Warning 1265 Data truncated for column 'utf8mb4_encoding' at row 1

2139

SELECT u_decimal, hex(utf8mb4_encoding) FROM t1 ORDER BY utf8mb4_encoding COLLATE utf8mb4_general_ci, BINARY utf8mb4_encoding;

2140

u_decimal hex(utf8mb4_encoding)

2141

65131 EFB9ABF09D849EF09D859EF09D859EF09D8480F09D859FEFB9ABEFB9ABF09D85A0EFB9AB

2142

119040 F09D8480

2143

119070 F09D849E

2144

119134 F09D859E

2145

119247 F09D878F

2146

120607 F09D9C9F

2147

120735 F09D9E9F

2148

917999 F3A087AF

2149

1114111 F48FBFBF

2150

119070 F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480

2151

119070 F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480

2152

INSERT INTO t1 VALUES (1114111, x'f5808080');

2153

Warnings:

2154

Warning 1366 Incorrect string value: '\xF5\x80\x80\x80' for column 'utf8mb4_encoding' at row 1

2155

SELECT character_maximum_length, character_octet_length FROM information_schema.columns WHERE

2156

table_name= 't1' AND column_name= 'utf8mb4_encoding';

2157

character_maximum_length character_octet_length

2158

10 40

2159

DROP TABLE IF EXISTS t2;

2160

CREATE TABLE t2 (

2161

u_decimal int NOT NULL,

2162

utf8mb3_encoding VARCHAR(10) NOT NULL

2163

) CHARACTER SET utf8mb3;

2164

INSERT INTO t2 VALUES (42856, x'ea9da8');

2165

INSERT INTO t2 VALUES (65131, x'efb9ab');

2166

INSERT INTO t2 VALUES (1114111, x'f48fbfbf');

2167

Warnings:

2168

Warning 1366 Incorrect string value: '\xF4\x8F\xBF\xBF' for column 'utf8mb3_encoding' at row 1

2169

SELECT character_maximum_length, character_octet_length FROM information_schema.columns WHERE

2170

table_name= 't2' AND column_name= 'utf8mb3_encoding';

2171

character_maximum_length character_octet_length

2172

10 30

2173

UPDATE t2 SET utf8mb3_encoding= x'f48fbfbd' where u_decimal= 42856;

2174

Warnings:

2175

Warning 1366 Incorrect string value: '\xF4\x8F\xBF\xBD' for column 'utf8mb3_encoding' at row 1

2176

UPDATE t2 SET utf8mb3_encoding= _utf8mb4 x'ea9da8' where u_decimal= 42856;

2177

SELECT HEX(CONCAT(utf8mb4_encoding, _utf8 x'ea9da8')) FROM t1;

2178

HEX(CONCAT(utf8mb4_encoding, _utf8 x'ea9da8'))

2179

F09D8480EA9DA8

2180

F09D849EEA9DA8

2181

F09D859EEA9DA8

2182

F09D878FEA9DA8

2183

F09D9C9FEA9DA8

2184

F09D9E9FEA9DA8

2185

F48FBFBFEA9DA8

2186

F3A087AFEA9DA8

2187

F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EA9DA8

2188

EFB9ABF09D849EF09D859EF09D859EF09D8480F09D859FEFB9ABEFB9ABF09D85A0EFB9ABEA9DA8

2189

F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EA9DA8

2190

EA9DA8

2191

SELECT HEX(CONCAT(utf8mb4_encoding, utf8mb3_encoding)) FROM t1,t2;

2192

HEX(CONCAT(utf8mb4_encoding, utf8mb3_encoding))

2193

F09D8480EA9DA8

2194

F09D8480EFB9AB

2195

F09D8480

2196

F09D849EEA9DA8

2197

F09D849EEFB9AB

2198

F09D849E

2199

F09D859EEA9DA8

2200

F09D859EEFB9AB

2201

F09D859E

2202

F09D878FEA9DA8

2203

F09D878FEFB9AB

2204

F09D878F

2205

F09D9C9FEA9DA8

2206

F09D9C9FEFB9AB

2207

F09D9C9F

2208

F09D9E9FEA9DA8

2209

F09D9E9FEFB9AB

2210

F09D9E9F

2211

F48FBFBFEA9DA8

2212

F48FBFBFEFB9AB

2213

F48FBFBF

2214

F3A087AFEA9DA8

2215

F3A087AFEFB9AB

2216

F3A087AF

2217

F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EA9DA8

2218

F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EFB9AB

2219

F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480

2220

EFB9ABF09D849EF09D859EF09D859EF09D8480F09D859FEFB9ABEFB9ABF09D85A0EFB9ABEA9DA8

2221

EFB9ABF09D849EF09D859EF09D859EF09D8480F09D859FEFB9ABEFB9ABF09D85A0EFB9ABEFB9AB

2222

EFB9ABF09D849EF09D859EF09D859EF09D8480F09D859FEFB9ABEFB9ABF09D85A0EFB9AB

2223

F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EA9DA8

2224

F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EFB9AB

2225

F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480

2226

EA9DA8

2227

EFB9AB

2228

2229

SELECT count(*) FROM t1, t2

2230

WHERE t1.utf8mb4_encoding > t2.utf8mb3_encoding;

2231

count(*)

2232

2233

ALTER TABLE t1 CONVERT TO CHARACTER SET utf8;

2234

Warnings:

2235

Warning 1366 Incorrect string value: '\xF0\x9D\x84\x80' for column 'utf8mb4_encoding' at row 1

2236

Warning 1366 Incorrect string value: '\xF0\x9D\x84\x9E' for column 'utf8mb4_encoding' at row 2

2237

Warning 1366 Incorrect string value: '\xF0\x9D\x85\x9E' for column 'utf8mb4_encoding' at row 3

2238

Warning 1366 Incorrect string value: '\xF0\x9D\x87\x8F' for column 'utf8mb4_encoding' at row 4

2239

Warning 1366 Incorrect string value: '\xF0\x9D\x9C\x9F' for column 'utf8mb4_encoding' at row 5

2240

Warning 1366 Incorrect string value: '\xF0\x9D\x9E\x9F' for column 'utf8mb4_encoding' at row 6

2241

Warning 1366 Incorrect string value: '\xF4\x8F\xBF\xBF' for column 'utf8mb4_encoding' at row 7

2242

Warning 1366 Incorrect string value: '\xF3\xA0\x87\xAF' for column 'utf8mb4_encoding' at row 8

2243

Warning 1366 Incorrect string value: '\xF0\x9D\x84\x9E\xF0\x9D...' for column 'utf8mb4_encoding' at row 9

2244

Warning 1366 Incorrect string value: '\xF0\x9D\x84\x9E\xF0\x9D...' for column 'utf8mb4_encoding' at row 10

2245

Warning 1366 Incorrect string value: '\xF0\x9D\x84\x9E\xF0\x9D...' for column 'utf8mb4_encoding' at row 11

2246

SHOW CREATE TABLE t1;

2247

Table Create Table

2248

t1 CREATE TABLE `t1` (

2249

`u_decimal` int(11) NOT NULL,

2250

`utf8mb4_encoding` varchar(10) NOT NULL

2251

) ENGINE=MyISAM DEFAULT CHARSET=utf8

2252

SELECT u_decimal,hex(utf8mb4_encoding),utf8mb4_encoding FROM t1;

2253

u_decimal hex(utf8mb4_encoding) utf8mb4_encoding

2254

119040 3F ?

2255

119070 3F ?

2256

119134 3F ?

2257

119247 3F ?

2258

120607 3F ?

2259

120735 3F ?

2260

1114111 3F ?

2261

917999 3F ?

2262

119070 3F3F3F3F3F3F3F3F3F3F ??????????

2263

65131 EFB9AB3F3F3F3F3FEFB9ABEFB9AB3FEFB9AB ﹫?????﹫﹫?﹫

2264

119070 3F3F3F3F3F3F3F3F3F3F ??????????

2265

1114111

2266

ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4;

2267

SHOW CREATE TABLE t2;

2268

Table Create Table

2269

t2 CREATE TABLE `t2` (

2270

`u_decimal` int(11) NOT NULL,

2271

`utf8mb3_encoding` varchar(10) NOT NULL

2272

) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

2273

SELECT u_decimal,hex(utf8mb3_encoding) FROM t2;

2274

u_decimal hex(utf8mb3_encoding)

2275

42856 EA9DA8

2276

65131 EFB9AB

2277

1114111

2278

ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb3;

2279

SHOW CREATE TABLE t2;

2280

Table Create Table

2281

t2 CREATE TABLE `t2` (

2282

`u_decimal` int(11) NOT NULL,

2283

`utf8mb3_encoding` varchar(10) NOT NULL

2284

) ENGINE=MyISAM DEFAULT CHARSET=utf8

2285

SELECT u_decimal,hex(utf8mb3_encoding) FROM t2;

2286

u_decimal hex(utf8mb3_encoding)

2287

42856 EA9DA8

2288

65131 EFB9AB

2289

1114111

2290

ALTER TABLE t1 MODIFY utf8mb4_encoding VARCHAR(10) CHARACTER SET utf8mb3;

2291

SHOW CREATE TABLE t1;

2292

Table Create Table

2293

t1 CREATE TABLE `t1` (

2294

`u_decimal` int(11) NOT NULL,

2295

`utf8mb4_encoding` varchar(10) DEFAULT NULL

2296

) ENGINE=MyISAM DEFAULT CHARSET=utf8

2297

SELECT u_decimal,hex(utf8mb4_encoding) FROM t1;

2298

u_decimal hex(utf8mb4_encoding)

2299

119040 3F

2300

119070 3F

2301

119134 3F

2302

119247 3F

2303

120607 3F

2304

120735 3F

2305

1114111 3F

2306

917999 3F

2307

119070 3F3F3F3F3F3F3F3F3F3F

2308

65131 EFB9AB3F3F3F3F3FEFB9ABEFB9AB3FEFB9AB

2309

119070 3F3F3F3F3F3F3F3F3F3F

2310

1114111

2311

ALTER TABLE t1 MODIFY utf8mb4_encoding VARCHAR(10) CHARACTER SET utf8mb4;

2312

SHOW CREATE TABLE t1;

2313

Table Create Table

2314

t1 CREATE TABLE `t1` (

2315

`u_decimal` int(11) NOT NULL,

2316

`utf8mb4_encoding` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL

2317

) ENGINE=MyISAM DEFAULT CHARSET=utf8

2318

SELECT u_decimal,hex(utf8mb4_encoding) FROM t1;

2319

u_decimal hex(utf8mb4_encoding)

2320

119040 3F

2321

119070 3F

2322

119134 3F

2323

119247 3F

2324

120607 3F

2325

120735 3F

2326

1114111 3F

2327

917999 3F

2328

119070 3F3F3F3F3F3F3F3F3F3F

2329

65131 EFB9AB3F3F3F3F3FEFB9ABEFB9AB3FEFB9AB

2330

119070 3F3F3F3F3F3F3F3F3F3F

2331

1114111

2332

ALTER TABLE t2 MODIFY utf8mb3_encoding VARCHAR(10) CHARACTER SET utf8mb4;

2333

SHOW CREATE TABLE t2;

2334

Table Create Table

2335

t2 CREATE TABLE `t2` (

2336

`u_decimal` int(11) NOT NULL,

2337

`utf8mb3_encoding` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL

2338

) ENGINE=MyISAM DEFAULT CHARSET=utf8

2339

SELECT u_decimal,hex(utf8mb3_encoding) FROM t2;

2340

u_decimal hex(utf8mb3_encoding)

2341

42856 EA9DA8

2342

65131 EFB9AB

2343

1114111

2344

DROP TABLE IF EXISTS t3;

2345

CREATE TABLE t3 (

2346

u_decimal int NOT NULL,

2347

utf8mb3_encoding VARCHAR(10) NOT NULL

2348

) CHARACTER SET utf8;

2349

INSERT INTO t3 SELECT * FROM t1;

2350

DROP TABLE IF EXISTS t4;

2351

CREATE TABLE t4 (

2352

u_decimal int NOT NULL,

2353

utf8mb4_encoding VARCHAR(10) NOT NULL

2354

) CHARACTER SET utf8mb4;

2355

INSERT INTO t3 SELECT * FROM t2;

2356

DROP TABLE t1;

2357

DROP TABLE t2;

2358

DROP TABLE t3;

2359

DROP TABLE t4;

2360

2361

# Testing that mixing utf8 and utf8mb4 collations returns utf8mb4

2362

2363

SELECT CHARSET(CONCAT(_utf8mb4'a',_utf8'b'));

2364

CHARSET(CONCAT(_utf8mb4'a',_utf8'b'))

2365

utf8mb4

2366

CREATE TABLE t1 (utf8mb4 VARCHAR(10) CHARACTER SET utf8mb4 NOT NULL);

2367

INSERT INTO t1 VALUES (x'ea9da8'),(x'f48fbfbf');

2368

SELECT CONCAT(utf8mb4, _utf8 x'ea9da8') FROM t1 LIMIT 0;

2369

CONCAT(utf8mb4, _utf8 x'ea9da8')

2370

CREATE TABLE t2 (utf8mb3 VARCHAR(10) CHARACTER SET utf8mb3 NOT NULL);

2371

INSERT INTO t2 VALUES (x'ea9da8');

2372

SELECT HEX(CONCAT(utf8mb4, utf8mb3)) FROM t1,t2 ORDER BY 1;

2373

HEX(CONCAT(utf8mb4, utf8mb3))

2374

EA9DA8EA9DA8

2375

F48FBFBFEA9DA8

2376

SELECT CHARSET(CONCAT(utf8mb4, utf8mb3)) FROM t1, t2 LIMIT 1;

2377

CHARSET(CONCAT(utf8mb4, utf8mb3))

2378

utf8mb4

2379

CREATE TEMPORARY TABLE t3 AS SELECT *, concat(utf8mb4,utf8mb3) FROM t1, t2;

2380

SHOW CREATE TABLE t3;

2381

Table Create Table

2382

t3 CREATE TEMPORARY TABLE `t3` (

2383

`utf8mb4` varchar(10) CHARACTER SET utf8mb4 NOT NULL,

2384

`utf8mb3` varchar(10) CHARACTER SET utf8 NOT NULL,

2385

`concat(utf8mb4,utf8mb3)` varchar(20) CHARACTER SET utf8mb4 NOT NULL DEFAULT ''

2386

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2387

DROP TEMPORARY TABLE t3;

2388

SELECT * FROM t1, t2 WHERE t1.utf8mb4 > t2.utf8mb3;

2389

utf8mb4 utf8mb3

2390

􏿿 Ꝩ

2391

SELECT * FROM t1, t2 WHERE t1.utf8mb4 = t2.utf8mb3;

2392

utf8mb4 utf8mb3

2393

Ꝩ Ꝩ

2394

SELECT * FROM t1, t2 WHERE t1.utf8mb4 < t2.utf8mb3;

2395

utf8mb4 utf8mb3

2396

DROP TABLE t1;

2397

DROP TABLE t2;

2398

2399

# Check that mixing utf8mb4 with an invalid utf8 constant returns error

2400

2401

CREATE TABLE t1 (utf8mb4 VARCHAR(10) CHARACTER SET utf8mb4);

2402

INSERT INTO t1 VALUES (x'f48fbfbf');

2403

SELECT CONCAT(utf8mb4, _utf8 '�') FROM t1;

2404

ERROR HY000: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat'

2405

SELECT CONCAT('a', _utf8 '�') FROM t1;

2406

ERROR HY000: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8_general_ci,COERCIBLE) for operation 'concat'

2407

DROP TABLE t1;

2408

2409

# Bug#51675 Server crashes on inserting 4 byte char.

2410

# after ALTER TABLE to 'utf8mb4'

2411

2412

SET NAMES utf8;

2413

CREATE TABLE t1 (

2414

subject varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci,

2415

p VARCHAR(15) CHARACTER SET utf8

2416

) DEFAULT CHARSET=latin1;

2417

ALTER TABLE t1 ADD INDEX (subject);

2418

ALTER TABLE t1

2419

DEFAULT CHARACTER SET utf8,

2420

MODIFY subject varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,

2421

MODIFY p varchar(255) CHARACTER SET utf8;

2422

SHOW CREATE TABLE t1;

2423

Table Create Table

2424

t1 CREATE TABLE `t1` (

2425

`subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,

2426

`p` varchar(255) DEFAULT NULL,

2427

KEY `subject` (`subject`)

2428

) ENGINE=MyISAM DEFAULT CHARSET=utf8

2429

INSERT INTO t1(subject) VALUES ('abcd');

2430

INSERT INTO t1(subject) VALUES(x'f0909080');

2431

DROP TABLE t1;

2432

2433

# Bug #51676 Server crashes on SELECT, ORDER BY on 'utf8mb4' column

2434

2435

SET NAMES utf8mb4;

2436

CREATE TABLE t1 (

2437

subject varchar(255) character set utf8mb4 collate utf8mb4_unicode_ci,

2438

p varchar(15) character set utf8mb4

2439

) DEFAULT CHARSET=latin1;

2440

INSERT INTO t1(subject) VALUES(0xF0909080);

2441

INSERT INTO t1(subject) VALUES(0x616263F0909080646566);

2442

SHOW CREATE TABLE t1;

2443

Table Create Table

2444

t1 CREATE TABLE `t1` (

2445

`subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,

2446

`p` varchar(15) CHARACTER SET utf8mb4 DEFAULT NULL

2447

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2448

SELECT * FROM t1 ORDER BY 1;

2449

subject p

2450

abc𐐀def NULL

2451

𐐀 NULL

2452

SELECT hex(subject), length(subject), char_length(subject), octet_length(subject) FROM t1 ORDER BY 1;

2453

hex(subject) length(subject) char_length(subject) octet_length(subject)

2454

616263F0909080646566 10 7 10

2455

F0909080 4 1 4

2456

SELECT subject FROM t1 ORDER BY 1;

2457

subject

2458

abc𐐀def

2459

𐐀

2460

DROP TABLE t1;

2461

2462

# End of 5.5 tests

2463

2464

2465

# End of tests

2466

Older »